May 10, 2012

sp_MSdrop_subscription_3rd (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSdrop_subscription_3rd(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db)

MetaData:

 CREATE PROCEDURE sys.sp_MSdrop_subscription_3rd  
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@subscriber sysname,
@subscriber_db sysname = NULL
)
AS
BEGIN
-- 'sp_MSdrop_subscription_3rd' is no longer supported.
RAISERROR(21023, 16, -1, 'sp_MSdrop_subscription_3rd')
RETURN 1
END

sp_MSdrop_merge_subscription (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSdrop_merge_subscription(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, nvarchar @subscription_type)

MetaData:

 CREATE PROCEDURE sys.sp_MSdrop_merge_subscription  
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@subscriber sysname,
@subscriber_db sysname,
@subscription_type nvarchar(15) = 'push' -- Subscription type - push, pull, both --
)
as
begin
set nocount on

declare @publisher_id smallint
declare @subscriber_id smallint
declare @retcode int
declare @publication_id int
declare @job_id binary(16)
declare @thirdparty_flag bit
declare @id int
declare @keep_for_last_run bit

--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end
--
-- security check
-- Has to be executed from distribution database
--
if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
begin
raiserror(21482, 16, -1, 'sp_MSdrop_merge_subscription', 'distribution')
return (1)
end
-- Check if publisher is a defined as a distribution publisher in the current database
exec @retcode = sys.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
if @retcode <> 0
begin
return(1)
end

-- Get the publication information
select @publication_id = publication_id,
@thirdparty_flag = thirdparty_flag
from dbo.MSpublications where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication
if @publication_id is NULL
begin
raiserror(20026, 16, -1, @publication)
return (1)
end

-- Check if subscriber exists
select @subscriber_id = srvid from master..sysservers where UPPER(srvname) = UPPER(@subscriber)
if @subscriber_id is NULL
begin
if not exists (select * from MSmerge_subscriptions where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication_id = @publication_id and
UPPER(subscriber) = UPPER(@subscriber) and
subscriber_db = @subscriber_db)
begin
raiserror (20032, 16, -1, @subscriber, @publisher)
return (1)
end
end

-- Check that subscription exists
if not exists (select * from dbo.MSmerge_subscriptions where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication_id = @publication_id and
UPPER(subscriber) = UPPER(@subscriber) and
subscriber_db = @subscriber_db)
begin
if @thirdparty_flag = 1
begin
-- UNDONE : Add this back again when we add pull subscriptions metedata at the distributor
-- raiserror (14050, 10, -1)
return(1)
end
else
return (0)
end

begin tran
save transaction MSdrop_merge_subscription

-- Delete the subscription
-- For anonymous type, delete virtual anonymous subscription also
-- if deleting the virtual subscription
-- (since there can be only one subscriber_id per article, subscriber_db doesn't matter)
delete from dbo.MSmerge_subscriptions where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication_id = @publication_id and
UPPER(subscriber) = UPPER(@subscriber) and
subscriber_db = @subscriber_db
if @@error <> 0
begin
goto FAILURE
end

--
-- Get agentid to check history record
--
select @id=id from dbo.MSmerge_agents where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication and
UPPER(subscriber_name) = UPPER(@subscriber) and
subscriber_db = @subscriber_db

--
-- If the subscription has not yet been synced, there is no need for subscriber side cleanup
-- therefore no need for the last agent run.
--
if exists (select * from dbo.MSmerge_history where agent_id = @id) and @subscription_type='push'
select @keep_for_last_run = 0 -- cleanup code is not activated.
else
select @keep_for_last_run = 0

--
-- Delete Merge agent and meta data, if it exists
--
EXECUTE @retcode = sys.sp_MSdrop_merge_agent
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@subscriber = @subscriber,
@subscriber_db = @subscriber_db,
@keep_for_last_run = @keep_for_last_run
if @@error <> 0 or @retcode <> 0
begin
goto FAILURE
end

commit transaction
return 0
FAILURE:
if @@trancount > 0
begin
ROLLBACK TRANSACTION MSdrop_merge_subscription
COMMIT TRANSACTION
end
return 1
end

sp_MSdrop_logreader_agent (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSdrop_logreader_agent(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication)

MetaData:

 create procedure sys.sp_MSdrop_logreader_agent   
(
@publisher sysname,
@publisher_db sysname,
@publication sysname -- Only used by 3rd party publisher
) AS
begin

SET NOCOUNT ON

--
-- Declarations.
--
DECLARE @retcode int
DECLARE @job_id binary(16)
DECLARE @job_step_uid uniqueidentifier
DECLARE @local_job bit
DECLARE @publisher_id smallint
DECLARE @name nvarchar(100)
DECLARE @agent_id int

--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end
--
-- security check
-- Has to be executed from distribution database
--
if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
begin
raiserror(21482, 16, -1, 'sp_MSdrop_logreader_agent', 'distribution')
return (1)
end
--
-- Initializations
--
select @publisher_id = srvid from master.dbo.sysservers where
UPPER(srvname) = UPPER(@publisher)


SELECT @job_id = job_id, @job_step_uid = job_step_uid, @local_job = local_job, @name = name, @agent_id = id FROM MSlogreader_agents WHERE
publisher_id = @publisher_id AND
publisher_db = @publisher_db AND
publication = @publication

-- Delete Perfmon instance
dbcc deleteinstance ("SQL Replication Logreader", @name)

-- Return if not exists
IF @local_job IS NULL
RETURN(0)

BEGIN TRAN

IF @local_job = 1
BEGIN
-- Don't drop the job for third party publications.
if exists (select * from msdb..MSdistpublishers where
UPPER(name) = UPPER(@publisher) and
thirdparty_flag = 0)
begin
IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE job_id = @job_id)
BEGIN
-- Checks if the job name matches one that is generated
-- by replication
EXEC @retcode = sys.sp_MSislogreaderjobnamegenerated
@publisher = @publisher,
@publisher_db = @publisher_db,
@job_id = @job_id
IF @@ERROR <> 0
GOTO UNDO

-- Only drop the job if the name was generated
IF @retcode = 0
BEGIN
EXEC @retcode = sys.sp_MSdrop_repl_job @job_id = @job_id,
@job_step_uid = @job_step_uid
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
END
END
end
END

DELETE MSlogreader_agents WHERE id = @agent_id
IF @@ERROR <> 0
GOTO UNDO

-- Remove history
DELETE MSlogreader_history WHERE
agent_id = @agent_id

IF @@ERROR <> 0
GOTO UNDO

COMMIT TRAN

RETURN(0)

UNDO:
if @@TRANCOUNT = 1
ROLLBACK TRAN
else
COMMIT TRAN
return(1)
end

sp_MSdrop_dynamic_snapshot_agent (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSdrop_dynamic_snapshot_agent(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, int @agent_id)

MetaData:

 create procedure sys.sp_MSdrop_dynamic_snapshot_agent   
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@agent_id int
)
AS
begin

SET NOCOUNT ON

--
-- Declarations.
--
DECLARE @retcode int
DECLARE @job_id binary(16)
DECLARE @job_step_uid uniqueidentifier
DECLARE @local_job bit
DECLARE @publisher_id smallint
DECLARE @name nvarchar(100)
declare @no_of_agents int

--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end
--
-- security check
-- Has to be executed from distribution database
--
if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
begin
raiserror(21482, 16, -1, 'sp_MSdrop_dynamic_snapshot_agent', 'distribution')
return (1)
end

--
-- Initializations
--
select @publisher_id = srvid from master.dbo.sysservers where
UPPER(srvname) = UPPER(@publisher)

SELECT @job_id = job_id, @job_step_uid = job_step_uid, @local_job = local_job, @name = name FROM MSsnapshot_agents WHERE
publisher_id = @publisher_id AND
publisher_db = @publisher_db AND
publication = @publication and
id = @agent_id

-- Delete Perfmon instance
dbcc deleteinstance ("SQL Replication Snapshot", @name)

-- Return if not exists
IF @local_job IS NULL
RETURN(0)

BEGIN TRAN

IF @local_job = 1
BEGIN
IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE job_id = @job_id)
BEGIN
EXEC @retcode = sys.sp_MSdrop_repl_job @job_id = @job_id,
@job_step_uid = @job_step_uid
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
END
END

DELETE MSsnapshot_agents WHERE id = @agent_id
IF @@ERROR <> 0
GOTO UNDO

-- Remove history
DELETE MSsnapshot_history WHERE agent_id = @agent_id
IF @@ERROR <> 0
GOTO UNDO

COMMIT TRAN
RETURN(0)

UNDO:
if @@TRANCOUNT = 1
ROLLBACK TRAN
else
COMMIT TRAN
return(1)
end

sp_MSdrop_publication (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSdrop_publication(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @alt_snapshot_folder
, bit @cleanup_orphans)

MetaData:

 CREATE PROCEDURE sys.sp_MSdrop_publication  
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@alt_snapshot_folder sysname = NULL,
@cleanup_orphans bit = 0 -- this is set when cleaning up
)
as
begin
set nocount on

declare @publisher_id smallint
,@publication_id int
,@retcode int
,@article sysname
,@article_id int
,@subscriber sysname
,@subscriber_db sysname
,@thirdparty_flag bit
,@working_dir nvarchar(255)
,@pub_dir nvarchar(255)

--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end
--
-- security check
-- Has to be executed from distribution database
--
if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
begin
raiserror(21482, 16, -1, 'sp_MSdrop_publication', 'distribution')
return (1)
end

-- Check if publisher is a defined as a distribution publisher in the current database
exec @retcode = sys.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
if @retcode <> 0
begin
return(1)
end

-- Make sure publication exists
select @publication_id = publication_id, @thirdparty_flag = thirdparty_flag
from dbo.MSpublications where publication = @publication and
publisher_id = @publisher_id and publisher_db = @publisher_db
if @publication_id is NULL
begin
-- We don't know whether or not it is a third party or not so we can not
-- return error.
-- raiserror(20026, 16, -1, @publication)
-- return (1)
return (0)
end

if (@cleanup_orphans = 0)
begin
-- Make sure that there are no subscriptions on the publication.
if exists (select * from dbo.MSsubscriptions s, dbo.MSpublications p where
p.publisher_id = @publisher_id and
p.publisher_db = @publisher_db and
p.publication = @publication and
s.publisher_id = @publisher_id and
s.publisher_db = @publisher_db and
s.publication_id = p.publication_id and
s.subscriber_id >= 0) -- ignore virtual subscriptions
begin
raiserror(14005, 16, -1)
return(1)
end
-- No real subscriptions exist, so delete any virtual subscriptions.
exec sys.sp_MSdrop_subscription
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@subscriber = null
end
else
begin
--
-- cleanup existing subscriptions since we are dropping this publication
--
declare #hcsubart cursor LOCAL FAST_FORWARD FOR
select s.article_id, upper(ss.name collate database_default), s.subscriber_db
from dbo.MSsubscriptions as s
join dbo.MSpublications as p
on s.publisher_id = p.publisher_id
and s.publisher_db = p.publisher_db
and s.publication_id = p.publication_id
and s.subscriber_id >= 0 -- ignore virtual subscriptions
join sys.servers as ss
on s.subscriber_id = ss.server_id
where p.publisher_id = @publisher_id
and p.publisher_db = @publisher_db
and p.publication = @publication

open #hcsubart
fetch #hcsubart into @article_id, @subscriber, @subscriber_db
while (@@fetch_status != -1)
begin
exec sys.sp_MSdrop_subscription
@publisher = @publisher,
@publisher_db = @publisher_db,
@subscriber = @subscriber,
@article_id = @article_id,
@subscriber_db = @subscriber_db,
@publication = @publication
fetch #hcsubart into @article_id, @subscriber, @subscriber_db
end
close #hcsubart
deallocate #hcsubart
-- delete any virtual subscriptions.
exec sys.sp_MSdrop_subscription
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@subscriber = null

end

SELECT @working_dir = working_directory FROM msdb..MSdistpublishers
where UPPER(name) = UPPER(@publisher)

IF @working_dir IS NOT NULL
BEGIN
-- Remove the pub dir under UNC and FTP if it exists
-- Note: sp_MSreplremoveuncdir will convert unc path to local path.
-- This is required. Otherwise we will see 'Access denied' error.
SELECT @pub_dir = @working_dir + '\unc\' +
fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 1) collate database_default
exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1
if @retcode <> 0 or @@error <> 0
return(1)

SELECT @pub_dir = @working_dir + '\ftp\' +
fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 1) collate database_default
exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1
if @retcode <> 0 or @@error <> 0
return(1)

SELECT @pub_dir = @working_dir + '\unc\' +
fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 0) collate database_default
exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1
if @retcode <> 0 or @@error <> 0
return(1)

SELECT @pub_dir = @working_dir + '\ftp\' +
fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 0) collate database_default
exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1
if @retcode <> 0 or @@error <> 0
return(1)

END

IF @alt_snapshot_folder IS NOT NULL AND RTRIM(@alt_snapshot_folder) <> N''
BEGIN

-- Make sure that alt_snapshot_folder is \ terminated
IF SUBSTRING(@alt_snapshot_folder,len(@alt_snapshot_folder),1) <> N'\'
BEGIN
SELECT @alt_snapshot_folder = @alt_snapshot_folder + N'\'
END

-- Remove the pub dir under UNC and FTP if it exists
-- Note: sp_MSreplremoveuncdir will convert unc path to local path.
-- This is required. Otherwise we will see 'Access denied' error.
SELECT @pub_dir = @alt_snapshot_folder + 'unc\' +
fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 1) collate database_default
exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1
if @retcode <> 0 or @@error <> 0
return(1)

SELECT @pub_dir = @alt_snapshot_folder + 'ftp\' +
fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 1) collate database_default
exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1
if @retcode <> 0 or @@error <> 0
return(1)

SELECT @pub_dir = @alt_snapshot_folder + 'unc\' +
fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 0) collate database_default
exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1
if @retcode <> 0 or @@error <> 0
return(1)

SELECT @pub_dir = @alt_snapshot_folder + 'ftp\' +
fn_replcomposepublicationsnapshotfolder(@publisher,@publisher_db,@publication, 0) collate database_default
exec @retcode = sys.sp_MSreplremoveuncdir @pub_dir, 1
if @retcode <> 0 or @@error <> 0
return(1)

END

begin tran
save tran MSdrop_publication

-- Delete all articles if a third party publication
if @thirdparty_flag = 1
begin
-- Delete all articles in the publication
declare hCarticles CURSOR LOCAL FAST_FORWARD FOR select article from MSarticles where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication_id =
(select publication_id from dbo.MSpublications where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication)
open hCarticles
fetch hCarticles into @article
while (@@fetch_status <> -1)
begin
exec @retcode = sys.sp_MSdrop_article @publisher, @publisher_db, @publication, @article
if @retcode != 0 or @@error != 0
begin
close hCarticles
deallocate hCarticles
goto UNDO
end

fetch hCarticles into @article
end
close hCarticles
deallocate hCarticles
end
--
-- remove threshold entries for this publication
--
delete dbo.MSpublicationthresholds
where publication_id = @publication_id
if @@error <> 0
goto UNDO
--
-- remove entry from dbo.MSpublications
--
delete from dbo.MSpublications where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication
if @@error <> 0
begin
raiserror (14006, 16, -1)
goto UNDO
end

-- Drop snapshot agent
exec @retcode = sys.sp_MSdrop_snapshot_agent
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication
if @@ERROR<> 0 or @retcode <> 0
goto UNDO

-- delete cache for this agent
delete MScached_peer_lsns
where agent_id in (select id
from MSdistribution_agents
where publisher_id = @publisher_id
and publisher_db = @publisher_db
and publication = @publication)
if @@ERROR<> 0
goto UNDO

-- Delete anonymous agents
delete MSdistribution_agents where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication
if @@ERROR<> 0 or @retcode <> 0
goto UNDO

delete from dbo.MSmerge_subscriptions
where publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication_id = @publication_id


delete dbo.MSmerge_articlehistory
from dbo.MSmerge_articlehistory arthist join dbo.MSmerge_sessions sess
on arthist.session_id=sess.session_id
where sess.agent_id in
(select id from dbo.MSmerge_agents where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication)
IF @@ERROR <> 0
GOTO UNDO

delete dbo.MSmerge_history
from dbo.MSmerge_history hist join dbo.MSmerge_sessions sess
on hist.session_id=sess.session_id
where sess.agent_id in
(select id from dbo.MSmerge_agents where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication)
IF @@ERROR <> 0
GOTO UNDO

delete dbo.MSrepl_errors
from dbo.MSrepl_errors errs join dbo.MSmerge_sessions sess
on errs.session_id=sess.session_id
where sess.agent_id in
(select id from dbo.MSmerge_agents where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication)
IF @@ERROR <> 0
GOTO UNDO

-- delete sessions entries
delete dbo.MSmerge_sessions where agent_id in
(select id from dbo.MSmerge_agents where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication)

delete dbo.MSmerge_agents where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication
if @@ERROR<> 0 or @retcode <> 0
goto UNDO

-- Cleanup publication access list table
delete dbo.MSpublication_access where
publication_id = @publication_id
if @@ERROR<> 0 or @retcode <> 0
goto UNDO

-- cleanup identity range allocation history information
delete dbo.MSmerge_identity_range_allocations
where publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication
if @@ERROR<> 0 or @retcode <> 0
goto UNDO

-- Remove publisher_id, publisher_db pair if no other publication is using it.
if not exists (select * from msdb.dbo.MSdistpublishers d, master.dbo.sysservers s
where s.srvid = @publisher_id
and upper(s.srvname) = upper(d.name) collate database_default
and upper(d.publisher_type) LIKE 'ORACLE%' )
and
not exists (select * from dbo.MSpublications where publisher_id = @publisher_id and
publisher_db = @publisher_db)
begin
declare @publisher_database_id int

select @publisher_database_id = id from MSpublisher_databases where
publisher_id = @publisher_id and
publisher_db = @publisher_db

delete from MSrepl_backup_lsns where
publisher_database_id = @publisher_database_id

delete from MSpublisher_databases where
publisher_id = @publisher_id and publisher_db = @publisher_db
if @@error <> 0
goto UNDO

-- Cleaning up MSrepl_originators
delete MSrepl_originators where
publisher_database_id = @publisher_database_id
if @@error <> 0
goto UNDO
end
--
-- commit all the work
--
commit tran
--
-- all done
--
return 0

UNDO:
if @@trancount > 0
begin
rollback tran MSdrop_publication
commit tran
end
return (1)
end

sp_MSdropconstraints (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSdropconstraints(nvarchar @table
, nvarchar @owner)

MetaData:

 --  This will be called merge at the subscriber side, check for dbo permission  
create procedure sys.sp_MSdropconstraints
@table sysname,
@owner sysname = null
as
declare @const_name nvarchar(258)
declare @objid int
declare @retcode int
declare @qualified_tablename nvarchar(517)
declare @quoted_tablename nvarchar(270)
declare @quoted_ownername nvarchar(270)

--
-- Check for subscribing permission
--
exec @retcode=sys.sp_MSreplcheck_subscribe
if @retcode<>0 or @@ERROR<>0 return (1)

select @quoted_tablename = quotename(@table)

if @owner is not NULL
begin
set @quoted_ownername = QUOTENAME(@owner)
set @qualified_tablename= @quoted_ownername + '.' + @quoted_tablename
end
else
set @qualified_tablename= @quoted_tablename

set @objid = object_id(@qualified_tablename)
if @objid is null
begin
if @owner is null
begin
select @objid = object_id from sys.objects
where name=@quoted_tablename
end
else
begin
select @objid = object_id from sys.objects
where name=@quoted_tablename and schema_name(schema_id)=@quoted_ownername
end
end
if @objid is NULL
return (1)

select @const_name = QUOTENAME(object_name(object_id)) from
sys.foreign_keys where parent_object_id = @objid

while @const_name is not null
begin
exec ('alter table ' + @qualified_tablename +
' drop constraint ' + @const_name)
if @@ERROR <> 0
return (1)
set @const_name = NULL
select @const_name = QUOTENAME(object_name(object_id)) from
sys.foreign_keys where parent_object_id = @objid
end

return (0)

sp_MSdropfkreferencingarticle (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSdropfkreferencingarticle(nvarchar @destination_object_name
, nvarchar @destination_owner_name)

MetaData:

 create procedure sys.sp_MSdropfkreferencingarticle (  
@destination_object_name sysname,
@destination_owner_name sysname = null
)
as
begin
set nocount on
declare @fk_name sysname,
@drop_command nvarchar(4000),
@parent_id int,
@retcode int,
@robject_name sysname,
@robject_schema sysname,
@transaction_opened bit,
@cursor_allocated bit,
@cursor_opened bit,
@timestamp datetime,
@program_name sysname,
@is_disabled bit,
@is_not_for_replication bit,
@is_not_trusted bit,
@delete_referential_action tinyint,
@update_referential_action tinyint,
@referenced_object_id int

select @retcode = 0,
@transaction_opened = 0,
@cursor_allocated = 0,
@timestamp = getdate()

select @program_name = program_name from sys.sysprocesses where spid = @@spid

-- Security check
exec @retcode = sys.sp_MSreplcheck_subscribe
if @@error <> 0 or @retcode <> 0
return 1

if @destination_owner_name is null
begin
-- Use default schema of the current user for the destination object
-- schema if one is not explicitly specified.
select @destination_owner_name = schema_name()
end

select @referenced_object_id = object_id(quotename(@destination_owner_name) + N'.' + quotename(@destination_object_name))

if @referenced_object_id is null return

if object_id('dbo.MSsavedforeignkeys', 'U') is null
begin
create table dbo.MSsavedforeignkeys
(

program_name sysname not null,
constraint_name sysname not null,
parent_schema sysname not null,
parent_name sysname not null,
referenced_object_schema sysname not null,
referenced_object_name sysname not null,
is_disabled bit not null,
is_not_for_replication bit not null,
is_not_trusted bit not null,
delete_referential_action tinyint not null,
update_referential_action tinyint not null,
timestamp datetime not null
)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

create clustered index ci_MSsavedforeignkeys
on dbo.MSsavedforeignkeys(program_name, constraint_name, parent_schema)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

create nonclustered index nci_MSsavedforeignkeys_timestamp
on dbo.MSsavedforeignkeys(timestamp)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

exec @retcode = dbo.sp_MS_marksystemobject 'dbo.MSsavedforeignkeys'
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
end


if object_id('dbo.MSsavedforeignkeycolumns', 'U') is null
begin
create table dbo.MSsavedforeignkeycolumns
(
program_name sysname not null,
constraint_name sysname not null,
parent_schema sysname not null,
constraint_column_id int not null,
referencing_column_name sysname not null,
referenced_column_name sysname not null,
timestamp datetime not null
)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

create clustered index ci_MSsavedforeignkeycolumns
on dbo.MSsavedforeignkeycolumns(program_name, constraint_name, parent_schema, constraint_column_id)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
create nonclustered index nci_MSsavedforeignkeycolumns_timestamp
on dbo.MSsavedforeignkeycolumns(timestamp)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

exec @retcode = dbo.sp_MS_marksystemobject 'dbo.MSsavedforeignkeycolumns'
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
end

if object_id('dbo.MSsavedforeignkeyextendedproperties', 'U') is null
begin
create table dbo.MSsavedforeignkeyextendedproperties
(
program_name sysname not null,
constraint_name sysname not null,
parent_schema sysname not null,
property_name sysname,
property_value sql_variant,
timestamp datetime not null
)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

create clustered index ci_MSsavedforeignkeyextendedproperties
on dbo.MSsavedforeignkeyextendedproperties(program_name, constraint_name, parent_schema)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

create nonclustered index nci_MSsavedforeignkeyextendedproperties_timestamp
on dbo.MSsavedforeignkeyextendedproperties(timestamp)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
exec @retcode = dbo.sp_MS_marksystemobject 'dbo.MSsavedforeignkeyextendedproperties'
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

end

begin transaction
save transaction sp_MSdropfkreferencingarticle
set @transaction_opened = 1

-- Remove stale foreign key entries
-- Globally remove anything that are more than 15 days old
-- Remove anything for the calling program more than 3 days old
delete dbo.MSsavedforeignkeys where timestamp < dateadd(day, -15, @timestamp)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
delete dbo.MSsavedforeignkeycolumns where timestamp < dateadd(day, -15, @timestamp)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
delete dbo.MSsavedforeignkeys where program_name = @program_name and timestamp < dateadd(day, -3, @timestamp)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
delete dbo.MSsavedforeignkeycolumns where program_name = @program_name and timestamp < dateadd(day, -3, @timestamp)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

declare hForeignKeys cursor local fast_forward
for
select name, parent_object_id, is_disabled, is_not_for_replication, is_not_trusted, delete_referential_action, update_referential_action
from sys.foreign_keys
where referenced_object_id = @referenced_object_id
set @cursor_allocated = 1

open hForeignKeys
set @cursor_opened = 1

fetch hForeignKeys into @fk_name, @parent_id, @is_disabled, @is_not_for_replication, @is_not_trusted, @delete_referential_action, @update_referential_action

while (@@fetch_status <> -1)
begin
select @robject_name = name, @robject_schema = schema_name(schema_id)
from sys.objects
where object_id = @parent_id
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

insert into dbo.MSsavedforeignkeys
(
program_name,
constraint_name,
parent_schema,
parent_name,
referenced_object_schema,
referenced_object_name,
is_disabled,
is_not_for_replication,
is_not_trusted,
delete_referential_action,
update_referential_action,
timestamp
)
values
(
@program_name,
@fk_name,
@robject_schema,
@robject_name,
@destination_owner_name,
@destination_object_name,
@is_disabled,
@is_not_for_replication,
@is_not_trusted,
@delete_referential_action,
@update_referential_action,
@timestamp
)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
insert into dbo.MSsavedforeignkeycolumns
(
program_name,
constraint_name,
parent_schema,
constraint_column_id,
referencing_column_name,
referenced_column_name,
timestamp
)
select @program_name,
@fk_name,
@robject_schema,
foreign_key_columns.constraint_column_id,
referencing_columns.name,
referenced_columns.name,
@timestamp
from sys.foreign_key_columns foreign_key_columns
inner join sys.columns referencing_columns
on foreign_key_columns.parent_column_id = referencing_columns.column_id and referencing_columns.object_id = @parent_id
inner join sys.columns referenced_columns
on foreign_key_columns.referenced_column_id = referenced_columns.column_id and referenced_columns.object_id = @referenced_object_id
where foreign_key_columns.constraint_object_id = object_id(quotename(@robject_schema) + N'.' + quotename(@fk_name), 'F')
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

insert into dbo.MSsavedforeignkeyextendedproperties
(
program_name,
constraint_name,
parent_schema,
property_name,
property_value,
timestamp
)
select @program_name,
@fk_name,
@robject_schema,
name,
value,
@timestamp
from fn_listextendedproperty(default, 'schema', @robject_schema, 'table', @robject_name, 'constraint', @fk_name)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
select @drop_command = N'alter table ' +
quotename(@robject_schema) + N'.' +
quotename(@robject_name) +
N' drop constraint ' + quotename(@fk_name)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
exec(@drop_command)
if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

fetch hForeignKeys into @fk_name, @parent_id, @is_disabled, @is_not_for_replication, @is_not_trusted, @delete_referential_action, @update_referential_action
end

close hForeignKeys
set @cursor_opened = 0

deallocate hForeignKeys
set @cursor_allocated = 0

commit transaction
set @transaction_opened = 0

Failure:

if @cursor_opened = 1
begin
close hForeignKeys
end

if @cursor_allocated = 1
begin
deallocate hForeignKeys
end

if @transaction_opened = 1
begin
rollback transaction sp_MSdropfkreferencingarticle
commit transaction
end
return @retcode
end

Total Pageviews