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_MSmakemetadataselectproc(nvarchar @tablename, nvarchar @ownername
, nvarchar @procname
, uniqueidentifier @pubid
, uniqueidentifier @artid
, bit @generate_subscriber_proc
, nvarchar @destination_owner)
MetaData:
create procedure sys.sp_MSmakemetadataselectproc (@tablename sysname, @ownername sysname, @procname sysname, @pubid uniqueidentifier, @artid uniqueidentifier, @generate_subscriber_proc bit = 0, @destination_owner sysname = NULL) as declare @retcode smallint declare @argname nvarchar(10) declare @varname nvarchar(10) declare @cmdpiece nvarchar(4000) declare @qualified_name nvarchar(270) declare @sync_objid int declare @tablenick int declare @rgcol nvarchar(140) declare @rowguidunionclause1 nvarchar(4000) declare @rowguidunionclause2 nvarchar(4000) declare @rowguidunionclause3 nvarchar(4000) declare @rowguidunionclause4 nvarchar(4000) declare @rowguiddeclareclause1 nvarchar(4000) declare @rowguiddeclareclause2 nvarchar(4000) declare @id int declare @maintainsmetadata bit set nocount on -- -- Check for dbo permission -- exec @retcode=sys.sp_MSreplcheck_subscribe if @retcode<>0 or @@ERROR<>0 return (1) if @ownername is NULL or @ownername='' select @qualified_name = QUOTENAME(@tablename) else select @qualified_name = QUOTENAME(@ownername) + '.' + QUOTENAME(@tablename) select @id = object_id(@qualified_name) select @rgcol = quotename(name) from sys.columns where object_id = @id and is_rowguidcol = 1 select @sync_objid=sync_objid, @tablenick = nickname from dbo.sysmergearticles where artid=@artid and pubid=@pubid if @tablenick is NULL return (1) if @generate_subscriber_proc = 1 begin select @qualified_name = quotename(@destination_owner) + '.' + quotename(@tablename) if exists (select 1 from dbo.sysmergearticles where artid=@artid and pubid=@pubid and upload_options in (1,2)) select @maintainsmetadata = 0 else select @maintainsmetadata = 1 end else begin set @maintainsmetadata= sys.fn_MSarticle_allows_DML_at_this_replica(@artid, default) end set @cmdpiece= 'SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON' exec (@cmdpiece) if @@error<>0 return(1) -- create temp table to select the command text out of declare @tempcmd table (step int identity NOT NULL, cmdtext nvarchar(4000) collate database_default null) select @rowguiddeclareclause1 = ' @rowguid1 uniqueidentifier, @rowguid2 uniqueidentifier = NULL, @rowguid3 uniqueidentifier = NULL, @rowguid4 uniqueidentifier = NULL, @rowguid5 uniqueidentifier = NULL, @rowguid6 uniqueidentifier = NULL, @rowguid7 uniqueidentifier = NULL, @rowguid8 uniqueidentifier = NULL, @rowguid9 uniqueidentifier = NULL, @rowguid10 uniqueidentifier = NULL, @rowguid11 uniqueidentifier = NULL, @rowguid12 uniqueidentifier = NULL, @rowguid13 uniqueidentifier = NULL, @rowguid14 uniqueidentifier = NULL, @rowguid15 uniqueidentifier = NULL, @rowguid16 uniqueidentifier = NULL, @rowguid17 uniqueidentifier = NULL, @rowguid18 uniqueidentifier = NULL, @rowguid19 uniqueidentifier = NULL, @rowguid20 uniqueidentifier = NULL, @rowguid21 uniqueidentifier = NULL, @rowguid22 uniqueidentifier = NULL, @rowguid23 uniqueidentifier = NULL, @rowguid24 uniqueidentifier = NULL, @rowguid25 uniqueidentifier = NULL, @rowguid26 uniqueidentifier = NULL, @rowguid27 uniqueidentifier = NULL, @rowguid28 uniqueidentifier = NULL, @rowguid29 uniqueidentifier = NULL, @rowguid30 uniqueidentifier = NULL, @rowguid31 uniqueidentifier = NULL, @rowguid32 uniqueidentifier = NULL, @rowguid33 uniqueidentifier = NULL, @rowguid34 uniqueidentifier = NULL, @rowguid35 uniqueidentifier = NULL, @rowguid36 uniqueidentifier = NULL, @rowguid37 uniqueidentifier = NULL, @rowguid38 uniqueidentifier = NULL, @rowguid39 uniqueidentifier = NULL, @rowguid40 uniqueidentifier = NULL, @rowguid41 uniqueidentifier = NULL, @rowguid42 uniqueidentifier = NULL, @rowguid43 uniqueidentifier = NULL, @rowguid44 uniqueidentifier = NULL, @rowguid45 uniqueidentifier = NULL, @rowguid46 uniqueidentifier = NULL, @rowguid47 uniqueidentifier = NULL, @rowguid48 uniqueidentifier = NULL, @rowguid49 uniqueidentifier = NULL, @rowguid50 uniqueidentifier = NULL,' select @rowguiddeclareclause2 = ' @rowguid51 uniqueidentifier = NULL, @rowguid52 uniqueidentifier = NULL, @rowguid53 uniqueidentifier = NULL, @rowguid54 uniqueidentifier = NULL, @rowguid55 uniqueidentifier = NULL, @rowguid56 uniqueidentifier = NULL, @rowguid57 uniqueidentifier = NULL, @rowguid58 uniqueidentifier = NULL, @rowguid59 uniqueidentifier = NULL, @rowguid60 uniqueidentifier = NULL, @rowguid61 uniqueidentifier = NULL, @rowguid62 uniqueidentifier = NULL, @rowguid63 uniqueidentifier = NULL, @rowguid64 uniqueidentifier = NULL, @rowguid65 uniqueidentifier = NULL, @rowguid66 uniqueidentifier = NULL, @rowguid67 uniqueidentifier = NULL, @rowguid68 uniqueidentifier = NULL, @rowguid69 uniqueidentifier = NULL, @rowguid70 uniqueidentifier = NULL, @rowguid71 uniqueidentifier = NULL, @rowguid72 uniqueidentifier = NULL, @rowguid73 uniqueidentifier = NULL, @rowguid74 uniqueidentifier = NULL, @rowguid75 uniqueidentifier = NULL, @rowguid76 uniqueidentifier = NULL, @rowguid77 uniqueidentifier = NULL, @rowguid78 uniqueidentifier = NULL, @rowguid79 uniqueidentifier = NULL, @rowguid80 uniqueidentifier = NULL, @rowguid81 uniqueidentifier = NULL, @rowguid82 uniqueidentifier = NULL, @rowguid83 uniqueidentifier = NULL, @rowguid84 uniqueidentifier = NULL, @rowguid85 uniqueidentifier = NULL, @rowguid86 uniqueidentifier = NULL, @rowguid87 uniqueidentifier = NULL, @rowguid88 uniqueidentifier = NULL, @rowguid89 uniqueidentifier = NULL, @rowguid90 uniqueidentifier = NULL, @rowguid91 uniqueidentifier = NULL, @rowguid92 uniqueidentifier = NULL, @rowguid93 uniqueidentifier = NULL, @rowguid94 uniqueidentifier = NULL, @rowguid95 uniqueidentifier = NULL, @rowguid96 uniqueidentifier = NULL, @rowguid97 uniqueidentifier = NULL, @rowguid98 uniqueidentifier = NULL, @rowguid99 uniqueidentifier = NULL, @rowguid100 uniqueidentifier = NULL' select @rowguidunionclause1 = ' select @rowguid1 as rowguid, 1 as sortcol union all select @rowguid2 as rowguid, 2 as sortcol union all select @rowguid3 as rowguid, 3 as sortcol union all select @rowguid4 as rowguid, 4 as sortcol union all select @rowguid5 as rowguid, 5 as sortcol union all select @rowguid6 as rowguid, 6 as sortcol union all select @rowguid7 as rowguid, 7 as sortcol union all select @rowguid8 as rowguid, 8 as sortcol union all select @rowguid9 as rowguid, 9 as sortcol union all select @rowguid10 as rowguid, 10 as sortcol union all select @rowguid11 as rowguid, 11 as sortcol union all select @rowguid12 as rowguid, 12 as sortcol union all select @rowguid13 as rowguid, 13 as sortcol union all select @rowguid14 as rowguid, 14 as sortcol union all select @rowguid15 as rowguid, 15 as sortcol union all select @rowguid16 as rowguid, 16 as sortcol union all select @rowguid17 as rowguid, 17 as sortcol union all select @rowguid18 as rowguid, 18 as sortcol union all select @rowguid19 as rowguid, 19 as sortcol union all select @rowguid20 as rowguid, 20 as sortcol union all select @rowguid21 as rowguid, 21 as sortcol union all select @rowguid22 as rowguid, 22 as sortcol union all select @rowguid23 as rowguid, 23 as sortcol union all select @rowguid24 as rowguid, 24 as sortcol union all select @rowguid25 as rowguid, 25 as sortcol union all select @rowguid26 as rowguid, 26 as sortcol union all select @rowguid27 as rowguid, 27 as sortcol union all select @rowguid28 as rowguid, 28 as sortcol union all select @rowguid29 as rowguid, 29 as sortcol union all select @rowguid30 as rowguid, 30 as sortcol union all select @rowguid31 as rowguid, 31 as sortcol union all' select @rowguidunionclause2 = ' select @rowguid32 as rowguid, 32 as sortcol union all select @rowguid33 as rowguid, 33 as sortcol union all select @rowguid34 as rowguid, 34 as sortcol union all select @rowguid35 as rowguid, 35 as sortcol union all select @rowguid36 as rowguid, 36 as sortcol union all select @rowguid37 as rowguid, 37 as sortcol union all select @rowguid38 as rowguid, 38 as sortcol union all select @rowguid39 as rowguid, 39 as sortcol union all select @rowguid40 as rowguid, 40 as sortcol union all select @rowguid41 as rowguid, 41 as sortcol union all select @rowguid42 as rowguid, 42 as sortcol union all select @rowguid43 as rowguid, 43 as sortcol union all select @rowguid44 as rowguid, 44 as sortcol union all select @rowguid45 as rowguid, 45 as sortcol union all select @rowguid46 as rowguid, 46 as sortcol union all select @rowguid47 as rowguid, 47 as sortcol union all select @rowguid48 as rowguid, 48 as sortcol union all select @rowguid49 as rowguid, 49 as sortcol union all select @rowguid50 as rowguid, 50 as sortcol union all select @rowguid51 as rowguid, 51 as sortcol union all select @rowguid52 as rowguid, 52 as sortcol union all select @rowguid53 as rowguid, 53 as sortcol union all select @rowguid54 as rowguid, 54 as sortcol union all select @rowguid55 as rowguid, 55 as sortcol union all select @rowguid56 as rowguid, 56 as sortcol union all select @rowguid57 as rowguid, 57 as sortcol union all select @rowguid58 as rowguid, 58 as sortcol union all select @rowguid59 as rowguid, 59 as sortcol union all select @rowguid60 as rowguid, 60 as sortcol union all select @rowguid61 as rowguid, 61 as sortcol union all select @rowguid62 as rowguid, 62 as sortcol union all' select @rowguidunionclause3 = ' select @rowguid63 as rowguid, 63 as sortcol union all select @rowguid64 as rowguid, 64 as sortcol union all select @rowguid65 as rowguid, 65 as sortcol union all select @rowguid66 as rowguid, 66 as sortcol union all select @rowguid67 as rowguid, 67 as sortcol union all select @rowguid68 as rowguid, 68 as sortcol union all select @rowguid69 as rowguid, 69 as sortcol union all select @rowguid70 as rowguid, 70 as sortcol union all select @rowguid71 as rowguid, 71 as sortcol union all select @rowguid72 as rowguid, 72 as sortcol union all select @rowguid73 as rowguid, 73 as sortcol union all select @rowguid74 as rowguid, 74 as sortcol union all select @rowguid75 as rowguid, 75 as sortcol union all select @rowguid76 as rowguid, 76 as sortcol union all select @rowguid77 as rowguid, 77 as sortcol union all select @rowguid78 as rowguid, 78 as sortcol union all select @rowguid79 as rowguid, 79 as sortcol union all select @rowguid80 as rowguid, 80 as sortcol union all select @rowguid81 as rowguid, 81 as sortcol union all select @rowguid82 as rowguid, 82 as sortcol union all select @rowguid83 as rowguid, 83 as sortcol union all select @rowguid84 as rowguid, 84 as sortcol union all select @rowguid85 as rowguid, 85 as sortcol union all select @rowguid86 as rowguid, 86 as sortcol union all select @rowguid87 as rowguid, 87 as sortcol union all select @rowguid88 as rowguid, 88 as sortcol union all select @rowguid89 as rowguid, 89 as sortcol union all select @rowguid90 as rowguid, 90 as sortcol union all select @rowguid91 as rowguid, 91 as sortcol union all select @rowguid92 as rowguid, 92 as sortcol union all select @rowguid93 as rowguid, 93 as sortcol union all' select @rowguidunionclause4 = ' select @rowguid94 as rowguid, 94 as sortcol union all select @rowguid95 as rowguid, 95 as sortcol union all select @rowguid96 as rowguid, 96 as sortcol union all select @rowguid97 as rowguid, 97 as sortcol union all select @rowguid98 as rowguid, 98 as sortcol union all select @rowguid99 as rowguid, 99 as sortcol union all select @rowguid100 as rowguid, 100 as sortcol' select @cmdpiece = ' create procedure dbo.' + QUOTENAME(@procname) + ' ( ' + @rowguiddeclareclause1 insert into @tempcmd (cmdtext) values (@cmdpiece) select @cmdpiece = @rowguiddeclareclause2 + ' ) ' insert into @tempcmd (cmdtext) values (@cmdpiece) select @cmdpiece = ' as begin declare @retcode int declare @maxversion int set nocount on if ({ fn ISPALUSER(''' + convert(nvarchar(36),@pubid) + ''') } <> 1) begin RAISERROR (14126, 11, -1) return (1) end select @maxversion= maxversion_at_cleanup from dbo.sysmergearticles where nickname = ' + cast(@tablenick as nvarchar(20)) + ' and pubid = ''' + convert(nvarchar(36),@pubid) + ''' ' insert into @tempcmd (cmdtext) values (@cmdpiece) if @maintainsmetadata = 1 begin select @cmdpiece = ' select case when (cont.generation is NULL and tomb.generation is null) then 0 else isnull(cont.generation, tomb.generation) end as generation, case when t.' + @rgcol + ' is null then (case when tomb.rowguid is NULL then 0 else tomb.type end) else (case when cont.rowguid is null then 3 else 2 end) end as type, case when tomb.rowguid is null then cont.lineage else tomb.lineage end as lineage, cont.colv1 as colv, @maxversion as maxversion, rows.rowguid as rowguid ' insert into @tempcmd (cmdtext) values (@cmdpiece) select @cmdpiece = ' from ( ' + @rowguidunionclause1 insert into @tempcmd (cmdtext) values (@cmdpiece) select @cmdpiece = @rowguidunionclause2 insert into @tempcmd (cmdtext) values (@cmdpiece) select @cmdpiece = @rowguidunionclause3 insert into @tempcmd (cmdtext) values (@cmdpiece) select @cmdpiece = @rowguidunionclause4 + ' ) as rows ' insert into @tempcmd (cmdtext) values (@cmdpiece) select @cmdpiece = ' left outer join ' + @qualified_name + ' t with (rowlock) on t.' + @rgcol + ' = rows.rowguid and rows.rowguid is not null left outer join dbo.MSmerge_contents cont with (rowlock) on cont.rowguid = rows.rowguid and cont.tablenick = ' + cast(@tablenick as nvarchar(20)) + ' left outer join dbo.MSmerge_tombstone tomb with (rowlock) on tomb.rowguid = rows.rowguid and tomb.tablenick = ' + cast(@tablenick as nvarchar(20)) + ' where rows.rowguid is not null order by rows.sortcol if @@error <> 0 return 1 end ' insert into @tempcmd (cmdtext) values (@cmdpiece) end else begin select @cmdpiece = ' select 0 as generation, case when t.' + @rgcol + ' is null then 0 else 3 end as type, NULL as lineage, NULL as colv, @maxversion as maxversion, rows.rowguid as rowguid ' insert into @tempcmd (cmdtext) values (@cmdpiece) select @cmdpiece = ' from ( ' + @rowguidunionclause1 insert into @tempcmd (cmdtext) values (@cmdpiece) select @cmdpiece = @rowguidunionclause2 insert into @tempcmd (cmdtext) values (@cmdpiece) select @cmdpiece = @rowguidunionclause3 insert into @tempcmd (cmdtext) values (@cmdpiece) select @cmdpiece = @rowguidunionclause4 + ' ) as rows ' insert into @tempcmd (cmdtext) values (@cmdpiece) select @cmdpiece = ' left outer join ' + @qualified_name + ' t with (rowlock) on t.' + @rgcol + ' = rows.rowguid and rows.rowguid is not null where rows.rowguid is not NULL order by rows.sortcol if @@error <> 0 return 1 end ' insert into @tempcmd (cmdtext) values (@cmdpiece) end select cmdtext from @tempcmd order by step -- drop table @tempcmd
No comments:
Post a Comment