May 7, 2012

sp_MSarticlecleanup (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_MSarticlecleanup(uniqueidentifier @pubid
, uniqueidentifier @artid
, bit @ignore_merge_metadata
, bit @force_preserve_rowguidcol)

MetaData:

 create procedure sys.sp_MSarticlecleanup  
@pubid uniqueidentifier,
@artid uniqueidentifier,
@ignore_merge_metadata bit = 0,
@force_preserve_rowguidcol bit = 0
as
set nocount on
declare @source_table nvarchar(517)
declare @ownername sysname
declare @objectname sysname
declare @tablenick int
declare @objid int
declare @sync_objid int
declare @view_type int
declare @tsview nvarchar(50)
declare @guidstr nvarchar(50)
declare @csview nvarchar(50)
declare @viewname nvarchar(517)
declare @retcode smallint
declare @qualified_name nvarchar(270)
declare @bi_tablename nvarchar(258)
declare @bi_viewname nvarchar(258)
declare @bi_procname nvarchar(258)
declare @before_table_view sysname
declare @merge_pub_markcolumn_bit int
declare @merge_pub_unmarkcolumn_bit int
declare @current_mappings_viewname sysname
declare @past_mappings_viewname sysname
declare @partition_view_name sysname
declare @repl_view_name nvarchar(258)
declare @before_upd_viewname nvarchar(258)
declare @preserve_rowguidcol bit
declare @constraintname nvarchar(258)
declare @genview sysname


-- to be called after article is set up in a subscriber

--
-- Security Check
--
EXEC @retcode = sys.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
return (1)

select @merge_pub_markcolumn_bit = 0x4000
select @merge_pub_unmarkcolumn_bit = ~@merge_pub_markcolumn_bit

select @objid = max(objid) from dbo.sysmergearticles where artid = @artid

if @objid is NULL
return 0

-- get owner name, and table name
select @objectname = name, @ownername = schema_name(schema_id)
from sys.objects where object_id = @objid

-- construct the qualified table name
select @source_table = QUOTENAME(@ownername) + '.' + QUOTENAME(@objectname)

exec @retcode=sys.sp_MSguidtostr @artid, @guidstr out
if @retcode<>0 or @@ERROR<>0 return (1)

-- get the insert, update and conflict proc names from dbo.sysmergearticles
select @sync_objid = sync_objid,
@view_type = view_type,
@tablenick = nickname,
@bi_tablename = object_name(before_image_objid),
@bi_viewname = object_name(before_view_objid),
@before_upd_viewname = object_name(before_upd_view_objid),
@preserve_rowguidcol = preserve_rowguidcol
from dbo.sysmergearticles where
pubid = @pubid and artid = @artid

-- set the article status to indicate it is in no other publication,
-- and it is about to be removed.
update dbo.sysmergearticles set status= 7
where pubid = @pubid and artid = @artid

-- drop the per article contents view for this article
exec @retcode= sys.sp_MSdropctsviews @pubid, @artid
if @@error<>0 or @retcode<>0
return 1

-- If there is a before image table, drop it and its cleanup proc --
if (@bi_tablename is not null)
begin
set @bi_procname = @bi_tablename + '_clean'
if exists (select * from sys.objects where
name = @bi_procname and type = 'P')
begin
select @bi_procname = QUOTENAME(@bi_procname)
exec ('drop proc ' + @bi_procname)
if @@ERROR<>0 return (1)
end

select @bi_tablename = QUOTENAME(@bi_tablename)
exec ('drop table ' + @bi_tablename)
if @@ERROR<>0 return (1)

-- delete the before table view created for access in the trigger
set @before_table_view = 'MSmerge_bivw_' + @guidstr
if exists (select * from sys.objects where type = 'V' and name = @before_table_view)
begin
exec (' drop view ' + @before_table_view)
if @@ERROR<>0 return (1)
end
end

-- If there is a before image view, drop it --
if (@bi_viewname is not null)
begin
select @bi_viewname = QUOTENAME(@bi_viewname)

exec ('drop view ' + @bi_viewname)
if @@ERROR<>0 return (1)
end

if (@before_upd_viewname is not null)
begin
select @before_upd_viewname = QUOTENAME(@before_upd_viewname)

exec ('drop view ' + @before_upd_viewname)
if @@ERROR<>0 return (1)
end

-- Drop the article procs --
exec @retcode=sys.sp_MSdroparticleprocs @pubid, @artid
if @@ERROR<>0 or @retcode<>0 return (1)

-- Drop the article-specific conflict table.
exec @retcode= sys.sp_MSdrop_article_conflict_table @pubid=@pubid, @artid=@artid
if @@error<>0 or @retcode<>0 return 1

-- Drop the article triggers --
exec @retcode=sys.sp_MSdroparticletriggers @objectname, @ownername
if @@ERROR<>0 or @retcode<>0 return (1)

exec @retcode=sys.sp_MSunmarkreplinfo @object=@objectname, @owner=@ownername
if @@ERROR<>0 or @retcode<>0 return (1)

-- If the article's has a temporary ( view type = 2) or a permanent view (view_type = 1 ) drop the sync object --
if (@objid <> @sync_objid)
begin
select @viewname = name from sys.objects where type='V' and is_ms_shipped = 1 and object_id = @sync_objid
if @viewname IS NOT NULL
begin
select @ownername = schema_name(schema_id) from sys.objects where name=@viewname
set @viewname = QUOTENAME(@ownername) + '.' + QUOTENAME(@viewname)
exec ('drop view ' + @viewname)
if @@ERROR<>0 return (1)
end
end

-- Drop repl_view_
select @repl_view_name = NULL
if object_id('dbo.sysmergepartitioninfo', 'U') is not NULL
begin
select @repl_view_name = QUOTENAME(object_name(repl_view_id)) from dbo.sysmergepartitioninfo WHERE artid = @artid AND pubid = @pubid
if object_id(@repl_view_name) is not NULL
begin
exec ('drop view ' + @repl_view_name)
end

select @repl_view_name = NULL
select @repl_view_name = QUOTENAME(object_name(logical_record_view)) from dbo.sysmergepartitioninfo WHERE artid = @artid AND pubid = @pubid
if @repl_view_name is not NULL
begin
exec ('drop view ' + @repl_view_name)
end
end

--
-- Drop the views created for MSmerge_contents and MSmerge_tombstone before dropping these two tables
--
set @csview = 'MSmerge_ctsv_' + @guidstr
set @tsview = 'MSmerge_tsvw_' + @guidstr
set @genview = 'MSmerge_genvw_' + @guidstr

if EXISTS (select * from sys.objects where name=@csview and type='V')
BEGIN
select @ownername = schema_name(schema_id) from sys.objects where name=@csview
select @viewname = QUOTENAME(@ownername) + '.' + QUOTENAME(@csview)
exec ('drop view ' + @viewname)
if @@ERROR<>0 return (1)
END

if EXISTS (select * from sys.objects where name=@tsview and type='V')
BEGIN
select @ownername = schema_name(schema_id) from sys.objects where name=@tsview
select @viewname = QUOTENAME(@ownername) + '.' + QUOTENAME(@tsview)
exec ('drop view ' + @viewname)
if @@ERROR<>0 return (1)
END

if EXISTS (select * from sys.objects where name=@genview and type='V')
BEGIN
select @ownername = schema_name(schema_id) from sys.objects where name=@genview
select @viewname = QUOTENAME(@ownername) + '.' + QUOTENAME(@genview)
exec ('drop view ' + @viewname)
if @@ERROR<>0 return (1)
END

--
-- Drop the views created for MSmerge_past_partition_mappings and MSmerge_past_current_mappings as well for this article
--
set @current_mappings_viewname = 'MSmerge_cpmv_' + @guidstr
set @past_mappings_viewname = 'MSmerge_ppmv_' + @guidstr
if EXISTS (select * from sys.objects where name=@current_mappings_viewname and type='V')
BEGIN
select @ownername = schema_name(schema_id) from sys.objects where name=@current_mappings_viewname
select @viewname = QUOTENAME(@ownername) + '.' + QUOTENAME(@current_mappings_viewname)
exec ('drop view ' + @viewname)
if @@ERROR<>0 return (1)
END

if EXISTS (select * from sys.objects where name=@past_mappings_viewname and type='V')
BEGIN
select @ownername = schema_name(schema_id) from sys.objects where name=@past_mappings_viewname
select @viewname = QUOTENAME(@ownername) + '.' + QUOTENAME(@past_mappings_viewname)
exec ('drop view ' + @viewname)
if @@ERROR<>0 return (1)
END


-- drop article specific partition views created by sp_MSpublicationview
doing it here since this is where we cleanup article specific views --
if object_id('dbo.sysmergepartitioninfoview', 'V') is not NULL
begin
select @partition_view_name = OBJECT_NAME(partition_view_id)
from dbo.sysmergepartitioninfoview where
pubid = @pubid and nickname = @tablenick
if @partition_view_name is not null
begin
select @ownername = schema_name(schema_id) from sys.objects where name=@partition_view_name
select @viewname = QUOTENAME(@ownername) + '.' + QUOTENAME(@partition_view_name)
exec ('drop view ' + @viewname)
if @@ERROR<>0 return (1)
end
end

-- if this article is published in another publication do not drop the identity constraint
-- do not delete the publisher entry from MSmerge_identity_range. Update the pubid
-- with the other pubid that exists
if object_id('dbo.MSmerge_identity_range', 'U') is not NULL
begin
exec @retcode = sys.sp_MSremoveidrangesupport @pubid, @artid, 0 -- @propagate_ddl_change --
IF @@ERROR <> 0 or @retcode <> 0
return 1
end

-- this code is to remove the old style identity range check constraints
if object_id('dbo.MSrepl_identity_range', 'U') is not NULL
begin
-- the following is needed bacause the schema of MSrepl_identity_range on distribution and subscribing
-- or publishing databases is different in shiloh
if exists (select 1 from sys.columns where object_id = object_id('dbo.MSrepl_identity_range', 'U') and name = 'objid')
begin
if exists (select 1 from dbo.MSrepl_identity_range where objid = @objid)
begin
-- drop the identity range check constraint. The % is here because in shiloh
-- we used either 'pub' or 'sub' or 'repub' to indicate the role of the replica.
-- here we only care about dropping the constraint
select @constraintname = 'repl_identity_range_%' + convert(nvarchar(36), @artid)
select @constraintname = REPLACE(@constraintname, '-', '_')
if exists (select 1 from sys.objects where type ='C' and name like @constraintname)
begin
select @constraintname = quotename(name) from sys.objects where name like @constraintname and type = 'C'
EXEC sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=1
exec ('alter table '+ @source_table + ' drop constraint ' + @constraintname)
-- don't worry about the error here
-- IF @@ERROR <> 0 return 1
EXEC sys.sp_MSsetcontext_merge_identityrangeddlbit @onoff=0
end
delete from dbo.MSrepl_identity_range where objid = @objid
end
end
end

-- Delete from contents, tombstone, delete conflicts; Ignore errors that occur --
if @ignore_merge_metadata = 0
begin
if object_id('dbo.MSmerge_current_partition_mappings') is not NULL
delete from dbo.MSmerge_current_partition_mappings where tablenick = @tablenick

if object_id('dbo.MSmerge_past_partition_mappings') is not NULL
delete from dbo.MSmerge_past_partition_mappings where tablenick = @tablenick

if object_id('MSmerge_contents') is not NULL
delete from dbo.MSmerge_contents where tablenick = @tablenick

if object_id('MSmerge_tombstone') is not NULL
delete from dbo.MSmerge_tombstone where tablenick = @tablenick

-- Delete rows from MSmerge_genhistory - if this is the last table that refers to them --
if not exists (select * from dbo.sysmergearticles where nickname = @tablenick and pubid <> @pubid)
begin
if object_id('MSmerge_generation_partition_mappings') is not NULL
delete from dbo.MSmerge_generation_partition_mappings
where generation in (select generation from dbo.MSmerge_genhistory where art_nick = @tablenick)

if object_id('MSmerge_genhistory') is not NULL
delete from dbo.MSmerge_genhistory where art_nick = @tablenick
end

if object_id('MSmerge_conflicts_info') is not NULL
delete from MSmerge_conflicts_info where tablenick = @tablenick
else if object_id('MSmerge_delete_conflicts') is not NULL
delete from MSmerge_delete_conflicts where tablenick = @tablenick
end

-- drop the rowguidcol if it was created by replication, and if there is no
-- other article on this table.
if 0=@preserve_rowguidcol and
0 = @force_preserve_rowguidcol and
not exists (select * from dbo.sysmergearticles where artid = @artid and pubid <> @pubid and status <> 7)
-- preseves rowguidcol if the table has filestream column
and not exists (select * from sys.columns where object_id = @objid and is_filestream = 1)
begin
select @ownername = schema_name(schema_id) from sys.objects where object_id = @objid
exec @retcode= sys.sp_MSdropguidcolumn @schemaname=@ownername, @tablename=@objectname
if @@error<>0 or @retcode<>0 return 1
end

-- reset sync tran bit if present. Do it here instead of just before applying bcp
if (object_id(@source_table, 'U') is not null)
begin
-- do not use @ownername in the following call because the @ownername has changed by this time. We use
-- this variable for saving owner names of several views that we drop, mostly owned by dbo. Thankfully,
-- we have the @source_table param that has the right value, and the proc does the right thing when @owner is N''
exec sys.sp_MSreset_synctran_bit @owner=N'', @table=@source_table
end

No comments:

Post a Comment

Total Pageviews