May 21, 2012

sp_MSmakemetadataselectproc (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_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

Total Pageviews