sp_MSmakeupdateproc (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_MSmakeupdateproc(nvarchar @tablename
, nvarchar @ownername
, nvarchar @procname
, uniqueidentifier @pubid
, uniqueidentifier @artid
, bit @generate_downlevel_procs
, bit @generate_subscriber_proc
, nvarchar @destination_owner)

MetaData:

   
--  This will be called by snapshot at publisher side and   
--  merge at the subscriber side, check for dbo permission  
  
--  usage pattern from CMergeDatasource should be:  
--  begin tran  
--  upd_sp_guid(1, 2, stream1, NULL, bitmap=0x07, setcheck=0x01) which check metadata   
--  upd_sp_guid(NULL, NULL, stream1, NULL, bitmap=0x07, setcheck=0x00)   
--  upd_sp_guid(NULL, NULL, NULL, stream2, bitmap=0x08, setcheck=0x02) which reset metadata  
--  commit  
  
create procedure sys.sp_MSmakeupdateproc   
    (@tablename sysname, @ownername sysname, @procname sysname, @pubid uniqueidentifier, @artid uniqueidentifier,  
     @generate_downlevel_procs bit = 0, @generate_subscriber_proc bit = 0, @destination_owner sysname = NULL)  
as  
declare @retcode            int  
declare @argname            nvarchar(10)  
declare @varname            nvarchar(10)  
declare @cmdpiece           nvarchar(4000)  
declare @qualified_name     nvarchar(270)  
declare @qualified_name2    nvarchar(270)  
declare @littlecomp         nvarchar(300)  
declare @id                 int  
declare @sync_objid         int  
declare @idstr              nvarchar(100)   
declare @fast_multicol_updateproc_bit bit  
declare @permissions_str    nvarchar(10)  
declare @permissions        int  
declare @partition_options  tinyint  
declare @maintainsmetadata  bit  
declare @escaped_qualified_name     nvarchar(270)  
declare @rgcol nvarchar(140)  
declare @logical_record_parent_nickname int  
declare @publication_number smallint  
declare @atpublisher            bit  
declare @maxschemaguid          uniqueidentifier  
declare @max_colv_size_in_bytes int  
declare @rgcolname          nvarchar(140)  
  
declare @colname nvarchar(140)  
declare @typename nvarchar(258)  
declare @base_typename nvarchar(140)  
declare @schname nvarchar(140)   -- track type owner name when the type is CLR UDT  
declare @system_type_id int  
declare @colid smallint --  index in sys.columns, used to iterate through sys.columns  
declare @colordinal smallint --  index in @setbm, used to interate actual data sent across  
declare @colordstr nvarchar(4) --  @colordinal stringification  
declare @xtype      int  
declare @iscomputed tinyint  
declare @isrowguidcol tinyint  
declare @separate_update_needed tinyint  
declare @update_stmt_started tinyint  
declare @isidentitycolumn bit  
declare @len smallint  
declare @blen smallint  
declare @prec int  
declare @scale int  
declare @tablenick int  
declare @tablenickstr nvarchar(12)  
declare @bytestr      nvarchar(10)  
declare @byteordinal  smallint  
declare @numbytes     smallint  
declare @bitstr       nvarchar(10)  
declare @has_updateable_columns_in_select_list bit  
declare @columnsetbitvarname sysname  
declare @is_indexing_column bit   
declare @indexing_columns_update_stmt_started bit  
declare @schema_option varbinary(8)  
  
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 @escaped_qualified_name = sys.fn_replreplacesinglequote(@qualified_name)  
  
  
select @id = object_id(@qualified_name)  
if @id is NULL return (1)  
  
if exists (select 1 from dbo.sysmergearticles where objid = @id and column_tracking = 1)  
begin  
    declare @current_col_count int, @missing_col_count int  
    select @current_col_count = count(*)   
            from sys.columns where object_id = @id  
    select @missing_col_count = coalesce((select max(missing_col_count)   
            from dbo.sysmergearticles where objid = @id), 0)  
    --  12 bytes per column in table + 1 trailing byte for colv.  
    select @max_colv_size_in_bytes = 12 * (@current_col_count + @missing_col_count) + 1  
    if @max_colv_size_in_bytes > 2953  
            select @max_colv_size_in_bytes = 2953  
end  
else  
    select @max_colv_size_in_bytes = 1  
          
select @rgcol = QUOTENAME(name) from sys.columns where object_id = @id and is_rowguidcol = 1  
if @rgcol is null  
    set @rgcol = 'rowguid'  
  
  
select @sync_objid = sync_objid, @fast_multicol_updateproc_bit = fast_multicol_updateproc, @permissions=check_permissions,  
    @permissions_str=convert(nvarchar(10), check_permissions), @schema_option=schema_option,  
    @tablenick=nickname  
    from dbo.sysmergearticles   
    where artid = @artid and pubid = @pubid  
if @tablenick is NULL  
    return (1)  
  
set @tablenickstr = rtrim(convert(nchar, @tablenick))  
  
select @partition_options = partition_options, @logical_record_parent_nickname = logical_record_parent_nickname  
from dbo.sysmergepartitioninfo  
where pubid = @pubid and artid = @artid  
  
select @maxschemaguid = sys.fn_GetArticleSchemaVersionGuid(@artid, @pubid)  
  
if @partition_options = 2  
begin  
    --  if this is a republisher of this article, and we are currently  
    --  downloading from the top-level publisher, then pretend that this is   
    --  not a well-partitioned article. This is done such that the partition evaluation  
    --  and setrowmetadata is done appropriately.  
    if sys.fn_MSmerge_islocalpubid(@pubid) = 0   
        and exists (select * from dbo.sysmergearticles   
                    where artid = @artid  
                    and sys.fn_MSmerge_islocalpubid(pubid) = 1)  
        select @partition_options = 0  
end  
  
set @idstr = rtrim(convert(nchar, @id))   
  
select @is_indexing_column = 0  
select @separate_update_needed = 0  
select @update_stmt_started = 0  
select @has_updateable_columns_in_select_list = 0  
select @indexing_columns_update_stmt_started = 0  
  
select @qualified_name2 = @qualified_name  
  
if @generate_subscriber_proc = 1  
begin  
    select @atpublisher = 0  
    select @qualified_name = quotename(@destination_owner) + '.' + quotename(@tablename)  
    select @escaped_qualified_name = sys.fn_replreplacesinglequote(@qualified_name)  
    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)  
    select @atpublisher = sys.fn_MSmerge_islocalpubid(@pubid)  
end  
  
select top 1 @publication_number = publication_number   
from dbo.sysmergepublications   
where pubid = @pubid  
  
--  create temp table to select the command text out of  
create table #tempcmd (phase int NOT NULL, step int identity NOT NULL, cmdtext nvarchar(4000) collate database_default null)  
create table #coltab (colname nvarchar(140), paramname nvarchar(10))  
  
--  insert text pieces that don''t repeat for each column  
  
--  phase 0 : create procedure and fixed part of argument list  
  
--  For Yukon version of this proc, add the @maxschemaguidforarticle as the first parameter  --   
if 0 = @generate_downlevel_procs AND @atpublisher = 1  
begin  
    if 1 = @maintainsmetadata  
    begin  
        set @cmdpiece = 'Create procedure dbo.' + quotename(@procname) + ' (@maxschemaguidforarticle uniqueidentifier, @rowguid uniqueidentifier, @setbm varbinary(125) = NULL,  
        @metadata_type tinyint, @lineage_old varbinary(311), @generation bigint,  
        @lineage_new varbinary(311), @colv varbinary(' + convert(nvarchar(13), @max_colv_size_in_bytes) + ') '  
    end  
    else  
    begin  
        set @cmdpiece = 'Create procedure dbo.' + @procname + ' (@maxschemaguidforarticle uniqueidentifier, @rowguid uniqueidentifier, @setbm varbinary(125) = NULL'  
    end  
end  
else  
begin  
    if 1 = @maintainsmetadata  
    begin  
        set @cmdpiece = 'Create procedure dbo.' + quotename(@procname) + ' (@rowguid uniqueidentifier, @setbm varbinary(125) = NULL,  
        @metadata_type tinyint, @lineage_old varbinary(311), @generation bigint,  
        @lineage_new varbinary(311), @colv varbinary(' + convert(nvarchar(13), @max_colv_size_in_bytes) + ') '  
    end  
    else  
    begin  
        set @cmdpiece = 'Create procedure dbo.' + @procname + ' (@rowguid uniqueidentifier, @setbm varbinary(125) = NULL'  
    end  
end  
  
insert into #tempcmd (phase, cmdtext) values (0, @cmdpiece)  
  
--  phase 1 is rest of argument list; goes in during loop over columns  
--  phase 2 paren to close argument list and fixed variable declarations  
if 1 = @maintainsmetadata  
begin  
    set @cmdpiece = ')  
as  
    declare @match int '  
end  
else  
begin  
    set @cmdpiece = ')  
as  
'  
end  
  
insert into #tempcmd (phase, cmdtext) values (2, @cmdpiece)  
  
select @cmdpiece = '  
    declare @fset int  
    declare @errcode int  
    declare @retcode smallint  
    declare @rowcount int  
    declare @error int  
    declare @hasperm bit  
    declare @tablenick int  
    declare @started_transaction bit  
    declare @indexing_column_updated bit  
    declare @publication_number smallint  
  
    set nocount on  
  
    if ({ fn ISPALUSER(''' + convert(nvarchar(36),@pubid) + ''') } <> 1)  
    begin  
        RAISERROR (14126, 11, -1)  
        return 4  
    end  
  
    select @started_transaction = 0  
    select @publication_number = ' + convert(nvarchar(10), @publication_number)+ '  
    select @tablenick = ' + @tablenickstr + '  
  
    if is_member(''db_owner'') = 1  
        select @hasperm = 1  
    else  
        select @hasperm = 0  
  
    select @indexing_column_updated = 0'  
  
insert into #tempcmd (phase, cmdtext) values (2, @cmdpiece)  
  
if @permissions>0  
begin  
    select @cmdpiece = '  
    exec @retcode = sys.sp_MSreplcheck_permission @objid = ' + @idstr + ', @type=2, @permissions = ' + @permissions_str + '  
    if @retcode<>0 or @@ERROR<>0 return (4)'  
    insert into #tempcmd (phase, cmdtext) values (2, @cmdpiece)  
end  
  
--  phase 2 - check the max schema guid for article and error out if they do not match  
if 0 = @generate_downlevel_procs AND @atpublisher = 1  
begin  
    set @cmdpiece= '  
    declare @maxschemaguid      uniqueidentifier  
    select @maxschemaguid = ''' + convert(nvarchar(36),@maxschemaguid) + '''  
    if ((@maxschemaguidforarticle IS NOT NULL) and (@maxschemaguid <> @maxschemaguidforarticle))  
    begin  
        RAISERROR (25007, 11, -1)  
        return 5  
    end'  
    insert into #tempcmd (phase, cmdtext) values (2, @cmdpiece)  
end  
  
  
--  phase 3 is rest of variable declarations; goes in during loop over columns  
--  phase 4 begin a transaction, set savepoint in case we roll back, begin select to get current values  
set @cmdpiece = '  
    if @@trancount = 0  
    begin  
        begin transaction sub  
        select @started_transaction = 1  
    end  
'  
insert into #tempcmd (phase, cmdtext) values (4, @cmdpiece)  
  
--   
--  Do not allow out of partition updates at the publisher.  
--  Check to see if the row being updated is in the partition  
--  before updating it.  
 --   
if @atpublisher = 1 AND @partition_options > 0  
begin  
    --  Get the column name for the rowguid column.  
    select @rgcolname = QUOTENAME(name) from sys.columns where object_id = @id and is_rowguidcol = 1  
    if @rgcolname is null  
        set @rgcolname = 'rowguid'  
    --  for > 0 partition_options we will not allow out of partition inserts  
    if exists (select * from dbo.sysmergearticles where pubid = @pubid and objid = @id  
                                            and len(subset_filterclause) > 0)  
                                    or exists (select * from dbo.sysmergesubsetfilters where pubid = @pubid and   
                                                    (art_nickname = @tablenick or join_nickname = @tablenick))  
    begin          
        select @cmdpiece = '  
    if not exists (select 1 from ' + QUOTENAME(OBJECT_NAME(sync_objid))  
            from dbo.sysmergearticles where pubid = @pubid and objid = @id  
        set @cmdpiece = @cmdpiece + ' where ' + @rgcolname + ' = @rowguid)   
    begin  
        raiserror(20733, 16, -1, ''' + sys.fn_replreplacesinglequote(@tablename) + ''', ' + convert(char(3), @partition_options) + ')  
        set @errcode= 3  
        goto Failure  
    end'  
        insert into #tempcmd (phase, cmdtext) values (4, @cmdpiece)  
    end  
end  
  
set @cmdpiece = '  
    select '  
insert into #tempcmd (phase, cmdtext) values (4, @cmdpiece)  
  
  
--  phase 5 is middle part of select assigning column values to local variables --  goes in loop  
--  we will only select columns that are part of a clustering/nonclustering index or is a partitioning column  
--  phase 10 --  finish the select, check that metadata matches  
set @cmdpiece= '        from ' + @qualified_name + ' where rowguidcol = @rowguid'  
  
if 1 = @maintainsmetadata  
begin  
    set @cmdpiece= @cmdpiece + '  
    set @match = NULL  
'  
end  
  
insert into #tempcmd (phase, cmdtext) values (10, @cmdpiece)  
  
--  If we are generating downlevel procs then convert lineage from 80 to 90 format.  
if (1 = @generate_downlevel_procs )  
begin  
    set @cmdpiece= '  
    if @compatlevel < 90 and @lineage_old is not null  
        set @lineage_old = {fn LINEAGE_80_TO_90(@lineage_old)}  
'  
    insert into #tempcmd (phase, cmdtext) values (10, @cmdpiece)  
end  
  
--  now do the loop over all columns and insert the missing pieces  
  
--  do not script out computed columns or timestamp columns  
--  we will not select the row from the base table if this article is column tracked.  
--  we will only rely on the @setbm parameter then. In the case that it is not column tracked  
--  we will only select out columns which are part of a clustered or NC index.  
--  ignore the computed, timestamp and identity colums since they cannot be updated.  
  
if @sync_objid = @id  
    select @colid = min (column_id) from sys.columns where object_id = @id and is_computed=0 and user_type_id<>type_id('timestamp')   
else  
    select @colid = min(cs.column_id)   
        from sys.columns cs inner join sys.columns co on co.object_id=@id and co.name = cs.name  
    where cs.object_id = @sync_objid and   
          co.is_computed=0 and co.user_type_id<>type_id('timestamp')   
  
select @colname = NULL  
select top 1 @colname = C.name,   
    @typename = type_name(C.user_type_id),   
    @base_typename = type_name(C.system_type_id),   
    @schname=case when S.name<>'sys' and S.name<>'dbo'  
                            then QUOTENAME(S.name)+'.' else '' end,       
    @system_type_id = C.system_type_id,  
    @blen = C.max_length,  
    @prec = C.precision,   
    @scale = C.scale  
    from sys.columns C    
            INNER JOIN sys.types T ON C.user_type_id = T.user_type_id  
            INNER JOIN sys.schemas S ON T.schema_id = S.schema_id              
    where C.object_id = @sync_objid and C.column_id = @colid  
      
select @isidentitycolumn = is_identity, @iscomputed=is_computed,   
    @xtype=user_type_id,  
    @isrowguidcol = is_rowguidcol  
    from sys.columns   
    where object_id = @id and name = @colname  
      
if ((@base_typename='nvarchar' or @base_typename='nchar') and @blen <> -1)  --  a unit of nchar takes 2 bytes  
    set @len = @blen/2  
else  
    set @len = @blen  
      
set @colordinal = 1  
declare @firstCol tinyint  
set @firstCol = 1  
declare @firstSelCol tinyint  
set @firstSelCol = 1  
declare @firstUpdCol tinyint  
set @firstUpdCol = 1  
declare @maplen smallint  
  
while (@colname is not null)  
begin    
    if @isidentitycolumn = 1 OR @iscomputed=1 OR type_name(@xtype)='timestamp'  
        goto Next_Column  
  
    set @colordstr = convert(nvarchar(4), @colordinal)  
  
    if @generate_subscriber_proc = 0  
        exec @retcode = sys.sp_MSmaptype @typename out, @len, @prec, @scale  
    else  
    begin  
        exec @retcode = sys.sp_MSmap_subscriber_type @xtype, @len, @prec, @scale, @schema_option, @typename out, @schname out, @maplen out  
        if (@maplen<>0) select @blen = @maplen  
    end  
          
    if @@ERROR <>0 OR @retcode <>0   
        return (1)  
  
    --  check if separate update statement is needed only if article supports fast multi-column updates.  
    --  reset @separate_update_needed  
    set @separate_update_needed = 0  
  
    -- check if this column is part of the filter or join filter clause.  
    --  if so, use a separater update statement for it rather than setting bitmask for the one cumulative update statement.  
      
    --  does updating this column change membership in a partial replica?   
    if exists (select * from dbo.sysmergearticles where objid = @id and sys.fn_MSisfilteredcolumn(subset_filterclause, @colname, @id) = 1)  
        set @separate_update_needed = 1  
    else if exists (select * from dbo.sysmergesubsetfilters where art_nickname = @tablenick and sys.fn_MSisfilteredcolumn(join_filterclause, @colname, @id) = 1)  
        set @separate_update_needed = 1  
    else if exists (select * from dbo.sysmergesubsetfilters where join_nickname = @tablenick and sys.fn_MSisfilteredcolumn(join_filterclause, @colname, @id) = 1)  
        set @separate_update_needed = 1  
      
    set @colname = QUOTENAME(@colname)  
  
    --  put in argument list element (phase 1)  
    set @argname = '@p' + rtrim(@colordstr)  
    set @cmdpiece = ',  
        ' + @argname + ' ' + @schname + @typename + ' = NULL '  
    insert into #tempcmd (phase, cmdtext) values (1, @cmdpiece)  
    insert into #coltab (colname, paramname) values (@colname, @argname)  
  
    if (@isrowguidcol = 1)  
        goto Next_Column  
  
    --  check if the current column is an indexing column  
    if exists (select * from sys.index_columns   
                where object_id = @id and   
                      column_id=@colid and   
                      index_id >= 1 and   
                      (partition_ordinal > 0 or key_ordinal > 0))  
        select @is_indexing_column = 1  
    else  
        select @is_indexing_column = 0  
  
  
    --  since blob columns cannot be used in indexing, it is ok to do just the seperate update statement in case of  
    --  blob columns and not worry about anything after that. we will do the blob column updates in phase 12  
    --  Filtering columns also need to get their separate updates, otherwise we cause a whole bunch of  
    --  false and unnecessary partition movements.  
    if (sys.fn_IsTypeBlob(@system_type_id,@len) <> 1) and (@separate_update_needed = 1 or @is_indexing_column = 1)  
    begin  
        set @varname = '@l' + rtrim(@colordstr)  
        if @firstSelCol=1  
        begin  
            set @firstSelCol= 0  
            set @cmdpiece= ''  
        end  
        else  
        begin  
            set @cmdpiece= ', '  
        end  
        set @cmdpiece = @cmdpiece + '  
        ' + @varname + ' = ' + @colname  
        insert into #tempcmd (phase, cmdtext) values (5, @cmdpiece)  
        select @has_updateable_columns_in_select_list = 1  
        set @cmdpiece = '  
    declare ' + @varname + ' ' + @schname + @typename  
        insert into #tempcmd (phase, cmdtext) values (3, @cmdpiece)  
    end  
  
    --  in phase 15 we will add comparison of old and new values  
    if (@typename like '%char%' or @base_typename like '%char%')  
    begin  
        --  Compare binaries instead of variables so that case changes are caught as different  
        set @littlecomp = 'convert(varbinary(' + rtrim(convert(nchar, @blen)) + '), ' + @argname + ')  
            = convert(varbinary(' + rtrim(convert(nchar, @blen)) + '), ' + @varname + ')'  
    end  
    else  
    begin  
        set @littlecomp = @argname + ' = ' + @varname  
    end  
          
    if (sys.fn_IsTypeBlob(@system_type_id,@len) = 1) or @separate_update_needed = 1   
    begin  
        if sys.fn_IsTypeBlob(@system_type_id,@len) = 1  
        begin  
            --  for text and image, we just test if argument is null and whether bit is set  
            select @cmdpiece =  '  
    if ' + @argname + ' is not null  
        set @fset = 1  
    else      
        exec @fset = sys.sp_MStestbit @setbm, ' + @colordstr  
        end  
        else  
        begin  
            set @cmdpiece = '  
    if ' + @littlecomp + '  
        set @fset = 0  
    else if ( ' + @varname + ' is null and ' + @argname + ' is null)   
        set @fset = 0  
    else if ' + @argname + ' is not null  
        set @fset = 1  
    else if @setbm = 0x0  
        set @fset = 0  
    else  
        exec @fset = sys.sp_MStestbit @setbm, ' + @colordstr          
        end  
          
        select @cmdpiece = @cmdpiece + '  
    if @fset <> 0  
    begin'  
        insert into #tempcmd (phase, cmdtext) values (12, @cmdpiece)   
          
        if @maintainsmetadata = 1 and @tablenick != @logical_record_parent_nickname  
            select @cmdpiece = '  
        if @match is NULL  
        begin  
            if @metadata_type = 3  
            begin  
                update ' + @qualified_name + ' set ' + @colname + ' = ' + @argname + '   
                from ' + @qualified_name + ' t   
                where t.' + @rgcol + ' = @rowguid and  
                   not exists (select 1 from dbo.MSmerge_contents c with (rowlock)  
                                where c.rowguid = @rowguid and   
                                      c.tablenick = ' + @tablenickstr + ')  
            end  
            else if @metadata_type = 2  
            begin  
                update ' + @qualified_name + ' set ' + @colname + ' = ' + @argname + '   
                from ' + @qualified_name + ' t   
                where t.' + @rgcol + ' = @rowguid and  
                      exists (select 1 from dbo.MSmerge_contents c with (rowlock)  
                                where c.rowguid = @rowguid and   
                                      c.tablenick = ' + @tablenickstr + ' and  
                                      c.lineage = @lineage_old)  
            end  
            else  
            begin  
                set @errcode=2  
                goto Failure  
            end  
        end  
        else  
        begin  
            update ' + @qualified_name + ' set ' + @colname + ' = ' + @argname + '   
                where rowguidcol = @rowguid  
        end  
        select @rowcount= @@rowcount, @error= @@error  
        if (@rowcount <> 1)  
        begin  
            set @errcode= 3  
            goto Failure  
        end  
        select @match = 1  
    end '  
        else  
            select @cmdpiece = '  
        update ' + @qualified_name + ' set ' + @colname + ' = ' + @argname + ' where rowguidcol = @rowguid   
        select @rowcount= @@rowcount, @error= @@error  
        if (@rowcount <> 1)  
        begin  
            set @errcode= 3  
            goto Failure  
        end  
    end'  
        --  Now insert the command to temp table  
        insert into #tempcmd (phase, cmdtext) values (12, @cmdpiece)   
        goto Next_Column  
    end  
  
    if @is_indexing_column = 1  
    begin  
        --  we will add this column to the select statement.  
        --  we will also add the variable declaration for this  
        if @is_indexing_column = 1  
        begin      
            select @columnsetbitvarname = '@iscol' + rtrim(@colordstr) + 'set'  
            set @cmdpiece = '  
    declare ' + @columnsetbitvarname + ' bit'  
            insert into #tempcmd (phase, cmdtext) values (3, @cmdpiece)  
          
            if @firstCol=1  
            begin  
                set @firstCol= 0  
                --  the following is added when we see the first indexing column. This is needed to build the   
                --  dynamic sql statement  
                select @cmdpiece = '         
    declare @firstUpdStmtCol bit  
    declare @nUpdateCols int  
    declare @updatestmt nvarchar(4000)   
      
    select @firstUpdStmtCol = 1  
    select @nUpdateCols = 0  
    select @updatestmt = ''update '' + ''' +  @escaped_qualified_name + ''' + '' set ''  
            '  
                insert into #tempcmd (phase, cmdtext) values (10, @cmdpiece)  
            end  
        end  
          
        set @cmdpiece = '  
    if ' + @littlecomp + '  
        set @fset = 0  
    else if ( ' + @varname + ' is null and ' + @argname + ' is null)   
        set @fset = 0  
    else if ' + @argname + ' is not null  
        set @fset = 1  
    else if @setbm = 0x0  
        set @fset = 0  
    else  
        exec @fset = sys.sp_MStestbit @setbm, ' + @colordstr + '  
    if @fset <> 0  
    begin'  
        insert into #tempcmd (phase, cmdtext) values (15, @cmdpiece)  
          
        declare @escaped_colname nvarchar(256)  
        select @escaped_colname = sys.fn_replreplacesinglequote(@colname)  
        set @cmdpiece = '  
        select @indexing_column_updated = 1  
        select ' + @columnsetbitvarname + ' = 1'  
        select @cmdpiece = @cmdpiece + '  
        if @firstUpdStmtCol = 1  
            select @firstUpdStmtCol = 0  
        else  
            select @updatestmt = @updatestmt + '','''  
        select @cmdpiece = @cmdpiece + '  
        select @updatestmt = @updatestmt + '''  
                + @escaped_colname + ' = ' + @argname + '''  
        select @nUpdateCols = @nUpdateCols + 1'  
        select @cmdpiece = @cmdpiece + '  
    end  
    else  
    begin  
        select ' + @columnsetbitvarname + ' = 0  
    end'  
        insert into #tempcmd (phase, cmdtext) values (15, @cmdpiece)  
  
        --  we need to construct the dynamic sql statement that we will be using incase the user has  
        --  permissions and we have to update an indexing column  
        if @indexing_columns_update_stmt_started = 0  
        begin  
            select @cmdpiece = '  
    if @indexing_column_updated = 1  
    begin  
        if @hasperm = 0  
        begin  
            update ' + @qualified_name + ' set '  
            insert into #tempcmd (phase, cmdtext) values (20, @cmdpiece)   
  
            --  phase 25 is all individual column updates  
  
            --  phase 30 below is end of non-dynamic sql update for non-indexing columns  
            if @maintainsmetadata = 1 and @tablenick != @logical_record_parent_nickname  
            begin  
                select @cmdpiece = '   
             from ' + @qualified_name + ' t   
                left outer join dbo.MSmerge_contents c with (rowlock)  
                    on c.rowguid = t.' + @rgcol + ' and   
                       c.tablenick = ' + @tablenickstr + ' and  
                       t.' + @rgcol + ' = @rowguid  
             where t.' + @rgcol + ' = @rowguid and  
             ((@match is not NULL and @match = 1) or   
              ((@metadata_type = 3 and c.rowguid is NULL) or  
               (@metadata_type = 2 and c.rowguid is not NULL and c.lineage = @lineage_old)))  
  
            select @rowcount= @@rowcount, @error= @@error'  
            end  
            else  
            begin  
                select @cmdpiece = ' where rowguidcol = @rowguid  
            select @rowcount= @@rowcount, @error= @@error'  
            end  
            insert into #tempcmd (phase, cmdtext) values (30, @cmdpiece)   
  
            --  now add the sp_executesql part              
            select @cmdpiece = '  
        end  
        else --  we can do sp_executesql since the current user has permissions to update the table  
        begin '  
            if @maintainsmetadata = 1 and @tablenick != @logical_record_parent_nickname  
                select @cmdpiece = @cmdpiece + '  
            if @match is NULL  
            begin  
                if @metadata_type = 3  
                begin  
                    select @updatestmt = @updatestmt + ''  
                       from ' + @escaped_qualified_name + ' t   
                       where t.' + @rgcol + ' = @rowguid and  
                             not exists (select 1 from dbo.MSmerge_contents c with (rowlock)  
                                         where c.rowguid = @rowguid and   
                                               c.tablenick = ' + @tablenickstr + ')''  
                end  
                else if @metadata_type = 2  
                begin  
                    select @updatestmt = @updatestmt + ''  
                       from ' + @escaped_qualified_name + ' t   
                       where t.' + @rgcol + ' = @rowguid and  
                             exists (select 1 from dbo.MSmerge_contents c with (rowlock)  
                                     where c.rowguid = @rowguid and   
                                           c.tablenick = ' + @tablenickstr + ' and  
                                           c.lineage = @lineage_old)''  
                end  
            end  
            else  
            begin  
                select @updatestmt = @updatestmt + ''  
                    where rowguidcol = @rowguid ''  
            end  
            select @updatestmt = @updatestmt + ''  
                select @rowcount = @@rowcount, @error = @@error''  
            exec sys.sp_executesql @stmt = @updatestmt, @parameters = N'''  
            else  
                select @cmdpiece = @cmdpiece + '  
            select @updatestmt = @updatestmt + ''  
                where rowguidcol = @rowguid  
                select @rowcount = @@rowcount, @error = @@error''  
            exec sys.sp_executesql @stmt = @updatestmt, @parameters = N'''  
                  
            insert into #tempcmd (phase, cmdtext) values (30, @cmdpiece)   
  
            --  phase 35 will be all the 'parameters' for sp_executesql  
  
            --  end of @parameters to sp_executesql  
            if @maintainsmetadata = 1 and @tablenick != @logical_record_parent_nickname  
                select @cmdpiece = ', @rowguid uniqueidentifier = ''''00000000-0000-0000-0000-000000000000'''', @lineage_old varbinary(311), @rowcount int output, @error int output'','  
            else  
                select @cmdpiece = ', @rowguid uniqueidentifier = ''''00000000-0000-0000-0000-000000000000'''', @rowcount int output, @error int output'','  
            insert into #tempcmd (phase, cmdtext) values (40, @cmdpiece)   
              
            --  phase 45 will be the actual parameters to sp_executesql  
  
            --  phase 50 is the end of actual parameters to sp_executesql  
            if @maintainsmetadata = 1 and @tablenick != @logical_record_parent_nickname  
                select @cmdpiece = '  
                    , @rowguid = @rowguid, @lineage_old = @lineage_old, @rowcount = @rowcount OUTPUT, @error = @error OUTPUT '  
            else  
                select @cmdpiece = '  
                    , @rowguid = @rowguid, @rowcount = @rowcount OUTPUT, @error = @error OUTPUT '  
            select @cmdpiece = @cmdpiece + '  
        end  --  end if @hasperm  
        if (@rowcount <> 1)  
        begin  
            set @errcode= 3  
            goto Failure  
        end'               
  
            if @maintainsmetadata = 1 and @tablenick != @logical_record_parent_nickname  
                select @cmdpiece = @cmdpiece + '      
        select @match = 1'  
          
            select @cmdpiece = @cmdpiece + '      
    end --  end if @indexing_column_updated '  
            insert into #tempcmd (phase, cmdtext) values (50, @cmdpiece)   
            select @indexing_columns_update_stmt_started = 1  
        end --  end if @indexing_columns_update_stmt_started  
        else  
        begin  
            select @cmdpiece = ','  
            insert into #tempcmd (phase, cmdtext) values (35, @cmdpiece)  
            select @cmdpiece = ','  
            insert into #tempcmd (phase, cmdtext) values (45, @cmdpiece)  
        end  
          
        --  add all the values in @parameters to the sp_executesql call  
        select @cmdpiece = '  
                    ' + @argname + ' ' + @schname + @typename  
        insert into #tempcmd (phase, cmdtext) values (35, @cmdpiece)  
  
        --  add all the @params for the sp_executesql call   
        select @cmdpiece = '  
                    ' + @argname + ' = ' + @argname  
        insert into #tempcmd (phase, cmdtext) values (45, @cmdpiece)  
  
        --  insert the part that goes into the update statement in the case we are not using execsql  
        --  this update statement is the one that updates all indexing columns.  
        if @firstUpdCol=1  
        begin  
            select @firstUpdCol = 0  
            select @cmdpiece = ''  
        end  
        else   
            select @cmdpiece = ','  
          
        if @maintainsmetadata = 1 and @tablenick != @logical_record_parent_nickname  
            select @cmdpiece = @cmdpiece + '  
                ' + @colname + ' = case ' + @columnsetbitvarname + ' when 1 then ' + @argname + ' else t.' + @colname + ' end'  
        else  
            select @cmdpiece = @cmdpiece + '  
                ' + @colname + ' = case ' + @columnsetbitvarname + ' when 1 then ' + @argname + ' else ' + @colname + ' end'  
        insert into #tempcmd (phase, cmdtext) values (25, @cmdpiece)  
    end  
    else  
    begin  
        --  if this is not an indexing column we can update the column as part of the general update column  
        if (@update_stmt_started = 0)  
        begin  
            select @update_stmt_started = 1  
            if @maintainsmetadata = 1 and @tablenick != @logical_record_parent_nickname  
            begin  
                select @cmdpiece = '  
    if @match is NULL  
    begin  
        update ' + @qualified_name + ' set '  
                insert into #tempcmd (phase, cmdtext) values (60, @cmdpiece)   
  
                --  phase 65 is the list of column updates  
                  
                select @cmdpiece = '   
         from ' + @qualified_name + ' t   
            left outer join dbo.MSmerge_contents c with (rowlock)  
                on c.rowguid = t.' + @rgcol + ' and   
                   c.tablenick = ' + @tablenickstr + ' and  
                   t.' + @rgcol + ' = @rowguid  
         where t.' + @rgcol + ' = @rowguid and  
         ((@match is not NULL and @match = 1) or   
          ((@metadata_type = 3 and c.rowguid is NULL) or  
           (@metadata_type = 2 and c.rowguid is not NULL and c.lineage = @lineage_old)))  
  
        select @rowcount= @@rowcount, @error= @@error  
    end  
    else  
    begin  
        update ' + @qualified_name + ' set '  
                insert into #tempcmd (phase, cmdtext) values (70, @cmdpiece)   
  
                --  phase 75 is the list of column updates  
                  
                select @cmdpiece = '   
         from ' + @qualified_name + ' t   
             where t.' + @rgcol + ' = @rowguid  
  
        select @rowcount= @@rowcount, @error= @@error  
    end'  
                insert into #tempcmd (phase, cmdtext) values (80, @cmdpiece)   
            end  
            else  
            begin  
                select @cmdpiece = '  
    update ' + @qualified_name + ' set '  
                insert into #tempcmd (phase, cmdtext) values (60, @cmdpiece)   
              
                --  phase 65 is the list of column updates  
                  
                select @cmdpiece = '   
         from ' + @qualified_name + ' t   
             where t.' + @rgcol + ' = @rowguid  
    select @rowcount= @@rowcount, @error= @@error'  
                insert into #tempcmd (phase, cmdtext) values (70, @cmdpiece)   
            end  
            select @cmdpiece = '  
    if (@rowcount <> 1) or (@error <> 0)  
    begin  
        set @errcode= 3  
        goto Failure  
    end'  
            insert into #tempcmd (phase, cmdtext) values (80, @cmdpiece)   
              
            if @maintainsmetadata = 1 and @tablenick != @logical_record_parent_nickname  
            begin  
                select @cmdpiece = '  
    select @match = 1'  
                insert into #tempcmd (phase, cmdtext) values (80, @cmdpiece)   
            end  
        end  
        else  
        begin  
            select @cmdpiece = ','  
            insert into #tempcmd (phase, cmdtext) values (65, @cmdpiece)  
            if @maintainsmetadata = 1 and @tablenick != @logical_record_parent_nickname  
                insert into #tempcmd (phase, cmdtext) values (75, @cmdpiece)  
        end  
  
        if @maintainsmetadata = 1 and @tablenick != @logical_record_parent_nickname  
        begin  
            select @cmdpiece = '  
            ' + @colname + ' = case when ' + @argname + ' is NULL then (case when sys.fn_IsBitSetInBitmask(@setbm, ' + @colordstr + ') <> 0 then ' + @argname + ' else t.' + @colname + ' end) else ' + @argname + ' end '  
            insert into #tempcmd (phase, cmdtext) values (65, @cmdpiece)          
            insert into #tempcmd (phase, cmdtext) values (75, @cmdpiece)          
        end  
        else  
        begin  
            select @cmdpiece = '  
            ' + @colname + ' = case when ' + @argname + ' is NULL then (case when sys.fn_IsBitSetInBitmask(@setbm, ' + @colordstr + ') <> 0 then ' + @argname + ' else t.' + @colname + ' end) else ' + @argname + ' end '  
            insert into #tempcmd (phase, cmdtext) values (65, @cmdpiece)          
        end  
    end  
      
Next_Column:          
    --  Advance loop to next column and repeat!  
    if @sync_objid = @id  
        select @colid = min (column_id) from sys.columns where object_id = @id and is_computed=0 and user_type_id<>type_id('timestamp') and column_id > @colid   
    else  
        select @colid = min(cs.column_id)   
            from sys.columns cs inner join sys.columns co on co.object_id=@id and co.name = cs.name  
        where cs.object_id = @sync_objid and   
              co.is_computed=0 and co.user_type_id<>type_id('timestamp') and cs.column_id > @colid   
      
    set @colname = NULL  
    if (@colid is not null)  
    begin  
        select top 1 @colname = C.name,   
        @typename = type_name(C.user_type_id),   
        @base_typename = type_name(C.system_type_id),   
        @schname=case when S.name<>'sys' and S.name<>'dbo'  
                                then QUOTENAME(S.name)+'.' else '' end,   
        @system_type_id = C.system_type_id,  
        @blen = C.max_length,  
        @prec = C.precision,   
        @scale = C.scale  
        from sys.columns C    
                INNER JOIN sys.types T ON C.user_type_id = T.user_type_id  
                INNER JOIN sys.schemas S ON T.schema_id = S.schema_id              
        where C.object_id = @sync_objid and C.column_id = @colid  
      
        select @isidentitycolumn = is_identity, @iscomputed=is_computed, @xtype=user_type_id,  
            @isrowguidcol = is_rowguidcol   
            from sys.columns   
            where object_id = @id and name = @colname  
                  
        if ((@base_typename='nvarchar' or @base_typename='nchar') and @blen <> -1) --  a unit of nchar takes 2 bytes  
            set @len = @blen/2  
        else  
            set @len = @blen  
  
        set @colordinal = @colordinal + 1  
    end  
end  
  
if 1 = @maintainsmetadata  
begin  
    if @partition_options > 1 and @atpublisher = 1 --  should really be @well_partitioned_multiple_hops = 1, requires agent changes in commit batch  
    begin  
        set @cmdpiece = ', @compatlevel int = 10, @partition_id int = NULL '  
        insert into #tempcmd (phase, cmdtext) values (1, @cmdpiece)  
    end  
    else  
    begin  
        set @cmdpiece = ', @compatlevel int = 10 '  
        insert into #tempcmd (phase, cmdtext) values (1, @cmdpiece)  
    end  
end  
  
--  Add dummy column list to select statement if there is no user updateable   
--  column.  
if @has_updateable_columns_in_select_list = 0  
begin  
    insert into #tempcmd (phase, cmdtext) values (3, N'declare @l int')  
    insert into #tempcmd (phase, cmdtext) values (5, N'@l = 1')  
end  
  
--  phase 20 finish the stored procedure  
if 1 = @maintainsmetadata  
begin  
  
    if @partition_options > 1  
        select @cmdpiece = '   
    if @lineage_new is not null  
    begin '  
    else  
        select @cmdpiece = ' '  
  
    set @cmdpiece = @cmdpiece + '  
    exec @retcode= sys.sp_MSsetrowmetadata   
        @tablenick, @rowguid, @generation,   
        @lineage_new, @colv, 2, NULL,   
        @compatlevel, 0, ''' + convert(nvarchar(36),@pubid) + ''''  
          
    if @partition_options > 1  
    begin  
        if @atpublisher = 1  
            select @cmdpiece = @cmdpiece + ',         
    @publication_number = @publication_number, @partition_id = @partition_id, @partition_options = 2 '  
        else  
            select @cmdpiece = @cmdpiece + ',         
    @publication_number = NULL, @partition_id = NULL, @partition_options = 2 '  
    end  
      
    select @cmdpiece = @cmdpiece + '  
    if @retcode<>0 or @@ERROR<>0  
    begin  
        set @errcode= 3  
        goto Failure  
    end '  
  
    if @partition_options > 1  
        select @cmdpiece = @cmdpiece + '   
    end '  
      
    select @cmdpiece = @cmdpiece + '  
  
delete from dbo.MSmerge_metadataaction_request  
    where tablenick=@tablenick and rowguid=@rowguid  
  
    if @started_transaction = 1  
        commit transaction  
  
  
    return(1)  
  
Failure:  
    -- rollback transaction sub  
    -- commit transaction  
    if @started_transaction = 1      
        rollback transaction  
  
'  
end  
else  
begin  
    set @cmdpiece = '  
    if @started_transaction = 1      
        commit transaction  
  
    delete from dbo.MSmerge_metadataaction_request  
        where tablenick=@tablenick and rowguid=@rowguid  
  
    return(1)  
  
Failure:  
    -- rollback transaction sub  
    -- commit transaction  
    if @started_transaction = 1      
        rollback transaction  
  
'  
end  
insert into #tempcmd (phase, cmdtext) values (120, @cmdpiece)  
  
if 1 = @maintainsmetadata  
begin  
    exec sys.sp_MScreatedupkeyupdatequery  
            @tablename= @qualified_name,  
            @tablenickstr= @tablenickstr,  
            @phase= 140,  
            @isconflictproc= 0,  
            @tablename2= @qualified_name2  
end  
  
set @cmdpiece = '  
    return @errcode'  
insert into #tempcmd (phase, cmdtext) values (200, @cmdpiece)  
  
--  Now we select out the command text pieces in proper order so that our caller,  
--  xp_execresultset will execute the command that creates the stored procedure.  
  
select cmdtext from #tempcmd order by phase, step  
drop table #tempcmd  
drop table #coltab  

sp_MSmap_partitionid_to_generations (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_MSmap_partitionid_to_generations(int @partition_id)

MetaData:

 create procedure sys.sp_MSmap_partitionid_to_generations @partition_id int  
as  
  
    declare @retcode smallint  
  
    exec @retcode = sys.sp_MSrepl_PAL_rolecheck  
    if (@retcode <> 0) or (@@error <> 0)  
        return 1  
  
    if exists (select * from dbo.MSmerge_contents)  
    begin  
        insert into dbo.MSmerge_generation_partition_mappings (publication_number, partition_id,   
                    generation, changecount)  
            select cpm.publication_number, cpm.partition_id, mc.generation, count(*)  
            from dbo.MSmerge_current_partition_mappings cpm join dbo.MSmerge_contents mc  
            on mc.tablenick = cpm.tablenick  
            and mc.rowguid = cpm.rowguid  
            and cpm.partition_id = @partition_id  
            group by cpm.publication_number, cpm.partition_id, mc.generation  
    end  
      
    insert into dbo.MSmerge_generation_partition_mappings (publication_number, partition_id,   
                    generation, changecount)  
            select ppm.publication_number, ppm.partition_id, ppm.generation, count(*)  
            from dbo.MSmerge_past_partition_mappings ppm  
            where ppm.partition_id = @partition_id  
            group by ppm.publication_number, ppm.partition_id, ppm.generation  
                          
    return 0  

sp_MSmakesystableviews (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_MSmakesystableviews(nvarchar @publication
, nvarchar @dynamic_snapshot_views_table_name
, bit @create_dynamic_views
, bigint @max_bcp_gen)

MetaData:

 --  Used by snapshot  
create procedure sys.sp_MSmakesystableviews (  
    @publication sysname,  
    @dynamic_snapshot_views_table_name sysname = null,  
    @create_dynamic_views bit = 0,  
    @max_bcp_gen bigint  
    )  
AS  
    declare @guidstr        nvarchar(40)  
    declare @pubid          uniqueidentifier  
    declare @contentsview_80    sysname   
    declare @tombstoneview  sysname  
    declare @genhistoryview_80  sysname  
    declare @filtersview_80    sysname  
    declare @filtersview_90    sysname  
    declare @contentsview_90    sysname  
    declare @contentsview_90_forall    sysname  
    declare @contentsview_90_forglobal    sysname  
    declare @rowtrackview    sysname  
    declare @genhistoryview_90  sysname   
    declare @retcode smallint  
    declare @dbname         sysname  
    declare @command        nvarchar(4000)  
    declare @dynamic_filters bit  
    declare @view_creation_command nvarchar(4000)  
    declare @newid  uniqueidentifier  
    declare @dynsnap_views bit  
    declare @compatlevel int  
    declare @need_ctsview_rowtrack bit  
    declare @need_ctsview_forglobal bit  
  
    --   
    --  Check to see if current publication has permission  
     --   
    exec @retcode=sys.sp_MSreplcheck_publish  
    if @retcode<>0 or @@ERROR<>0 return (1)  
  
    if (@dynamic_snapshot_views_table_name is not null and @dynamic_snapshot_views_table_name <> N'')  
        select @dynsnap_views = 1  
    else  
        select @dynsnap_views = 0  
  
    select @pubid = pubid, @dynamic_filters = dynamic_filters,   
           @compatlevel = backward_comp_level  
       from dbo.sysmergepublications where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()  
    if @pubid is null  
    BEGIN  
        RAISERROR (20026, 16, -1, @publication)  
        RETURN (1)  
    END  
      
    set @need_ctsview_rowtrack= sys.fn_MSmerge_mightneedrowtrackbcp(@pubid)  
    set @need_ctsview_forglobal= sys.fn_MSmerge_hasdownloadonlyarticles(@pubid)  
  
    --  check if there are any generations higher than what 80 can understand  
    --  if so return and not do any snapshot  
    if @compatlevel < 90  
    begin  
        declare @maxgen_80 int  
        set @maxgen_80= 2147483647  
        if exists (select generation from dbo.MSmerge_genhistory where generation > @maxgen_80)  
        begin  
            raiserror(21521,16,1,@maxgen_80)  
            return(1)  
        end  
    end  
  
    create table #temp_table_for_systable_view  
    (  
        id int identity(1,1),  
        contentsview sysname NULL,   
        tombstoneview sysname NULL,   
        genhistoryview sysname NULL,   
        filtersview sysname NULL,   
        contentsview_90_forall sysname NULL,   
        contentsview_90_forglobal sysname NULL,   
        genhistoryview_90 sysname NULL,   
        filtersview_90 sysname NULL,  
        contentsview_90 sysname NULL,  
        rowtrackview sysname NULL  
    )  
  
    --  use pubid to generate the view names only if we are not generating  
    --  dynamic snapshot views  
    if @dynsnap_views = 1 or @create_dynamic_views = 1  
        set @newid = newid()  
    else  
        set @newid = @pubid  
          
    exec @retcode = sys.sp_MSguidtostr @newid, @guidstr out  
    if @@ERROR<>0 OR @retcode<>0 return (1)  
  
    select @contentsview_80 = 'MSmerge_cont' + @guidstr  
    select @contentsview_90 = 'MSmerge_cont' + @guidstr + '_90'  
    select @contentsview_90_forall = 'MSmerge_cont' + @guidstr + '_90_forall'  
    select @contentsview_90_forglobal = 'MSmerge_cont' + @guidstr + '_90_forglobal'  
    select @tombstoneview = 'MSmerge_ts' + @guidstr  
    select @genhistoryview_80 = 'MSmerge_gh' + @guidstr  
    select @genhistoryview_90 = 'MSmerge_gh' + @guidstr + '_90'  
    select @filtersview_80 = 'MSmerge_filt' + @guidstr  
    select @filtersview_90 = 'MSmerge_filt' + @guidstr + '_90'  
    select @rowtrackview = 'MSmerge_cont' + @guidstr + '_rowtrack'  
  
    insert #temp_table_for_systable_view   
    (  
    contentsview,  
    tombstoneview,   
    genhistoryview,   
    filtersview,   
    contentsview_90_forall,   
    contentsview_90_forglobal,   
    genhistoryview_90,   
    filtersview_90,  
    contentsview_90,  
    rowtrackview  
    )  
    values  
    (  
        @contentsview_80,  
        @tombstoneview,  
        @genhistoryview_80,  
        @filtersview_80,  
        @contentsview_90_forall,  
        @contentsview_90_forglobal,  
        @genhistoryview_90,  
        @filtersview_90,  
        @contentsview_90,  
        @rowtrackview  
    )  
  
CreateViews:  
    --  if we generating a regular snapshot we need to drop the views if they exist  
    if @dynsnap_views = 0 and @create_dynamic_views = 0  
    begin  
        exec @retcode = sys.sp_MSdropsystableviews @pubid  
        if @@ERROR<>0 OR @retcode<>0 return (1)  
    end  
  
    set @guidstr = '''' + convert(nchar(36), @pubid) + ''''  
  
    --  generate view for MSmerge_contents qualified by the pubid  --   
    --  For dynamically filtered publication, security check is performed in  
       the sync view of the base table  --   
    --  only generate the views if there are rows in the MSmerge_contents table  
    if exists (select * from MSmerge_contents)  
    begin  
        set @command = 'sys.sp_MSmakectsview ' + QUOTENAME(@publication) + ' , ' + quotename(@contentsview_90)  
                            + ' , ' + COALESCE(quotename(@dynamic_snapshot_views_table_name) collate database_default, N'null' collate database_default)  
                            + ' , ' + convert(nvarchar, @dynsnap_views) + ' , ' + convert(nvarchar,@max_bcp_gen)  
        set @dbname = db_name()  
  
        exec @retcode = sys.xp_execresultset @command, @dbname  
        if @@ERROR<>0 OR @retcode <>0 return (1)  
    end  
    else  
    begin  
        set @command = 'create view dbo.' + quotename(@contentsview_90) + ' as select * from MSmerge_contents where 1 = 2'  
        exec(@command)  
                if @@ERROR<>0  
                        return (1)  
    end  
    exec @retcode = sys.sp_MS_marksystemobject  @contentsview_90   
    if @@ERROR<>0 or @retcode<>0  return (1)  
  
  
    --  Create the view on MSmerge_contents that contains rows which are updateable at every subscriber.  
    select @view_creation_command = 'create view dbo.' + quotename(@contentsview_90_forall) + ' as   
    select * from ' + quotename(@contentsview_90) + '  
        where tablenick in (select nickname from dbo.sysmergearticles where upload_options <> 1 and upload_options <> 2)    
            and generation <= ' + convert(nvarchar, @max_bcp_gen)  
  
    exec (@view_creation_command)  
    if @@ERROR <>0 return (1)  
    exec @retcode = sys.sp_MS_marksystemobject  @contentsview_90_forall   
    if @@ERROR<>0 or @retcode<>0  return (1)  
  
    if 1=@need_ctsview_forglobal  
    begin  
        --  Create the view on MSmerge_contents that contains rows which are only updateable at global subscribers.  
        select @view_creation_command = 'create view dbo.' + quotename(@contentsview_90_forglobal) + ' as   
        select * from ' + quotename(@contentsview_90) + '  
            where tablenick in (select nickname from dbo.sysmergearticles where upload_options = 1 or upload_options = 2)  
            and generation <= ' + convert(nvarchar, @max_bcp_gen)  
          
        exec (@view_creation_command)  
        if @@ERROR <>0 return (1)  
        exec @retcode = sys.sp_MS_marksystemobject  @contentsview_90_forglobal   
        if @@ERROR<>0 or @retcode<>0  return (1)  
    end  
  
    --  Create the view on MSmerge_contents that has the same format as the SQL2000 MSmerge_contents  --   
    --   dont convert. let mdac do all the conversion. we anyway raise an error above incase the generation greater than max_int  
    select @view_creation_command = 'create view dbo.' + @contentsview_80 + ' as   
    select tablenick, rowguid,   
    generation = case when abs(generation) > 2147483647 then 0 else convert(int, generation) end,   
    partchangegen = case when abs(partchangegen) > 2147483647 then 0 else convert(int, case when partchangegen < 0 then NULL else partchangegen end) end,   
    joinchangegen = case when abs(partchangegen) > 2147483647 then 0 else convert(int, case when partchangegen < 0 then (-partchangegen) else partchangegen end) end,   
    lineage = {fn LINEAGE_90_TO_80(lineage)},   
    colv1 = case   
        when colv1 is null or sys.fn_fIsColTracked(tablenick) = 0 then colv1   
        else {fn COLV_90_TO_80(colv1, sys.fn_cColvEntries_80(''' + cast(@pubid as nvarchar(36)) + ''', tablenick))} end  
    from ' + @contentsview_90  
     --   
  
    select @view_creation_command = 'create view dbo.' + quotename(@contentsview_80) + ' as   
    select tablenick, rowguid, generation = isnull(convert(int, generation),0),   
    partchangegen = case when partchangegen < 0 then NULL else convert(int, partchangegen) end,   
    joinchangegen = case when partchangegen < 0 then convert(int, (-partchangegen)) else convert(int, partchangegen) end,   
    lineage = {fn LINEAGE_90_TO_80(lineage)},   
    colv1 = case   
        when colv1 is null or sys.fn_fIsColTracked(tablenick) = 0 then colv1   
        else {fn COLV_90_TO_80(colv1, sys.fn_cColvEntries_80(''' + cast(@pubid as nvarchar(36)) + ''', tablenick))} end  
    from ' + quotename(@contentsview_90)  
  
    exec (@view_creation_command)  
    if @@ERROR <>0 return (1)  
    exec @retcode = sys.sp_MS_marksystemobject  @contentsview_80   
    if @@ERROR<>0 or @retcode<>0  return (1)  
  
    --    
    --  generate the view for dbo.MSmerge_tombstone. In SP2 and Shiloh, the change was made to make the view   
    --  return 0 rows since it is unnecessary and expensive to propagate the tombstones.  
    --  In order to leave all the other moving parts unchanged, we decided to let the view   
    --  return 0 rows.  
     --   
  
    select @view_creation_command = 'create view dbo.' + quotename(@tombstoneview) + ' as select * from dbo.MSmerge_tombstone where 1= 2'  
  
    if @dynamic_filters = 1  
    begin  
        select @view_creation_command = @view_creation_command + ' and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'  
    end  
  
    exec (@view_creation_command)  
    if @@ERROR <>0   
    begin  
        return (1)  
    end  
    exec @retcode = sys.sp_MS_marksystemobject  @tombstoneview   
    if @@ERROR<>0 or @retcode<>0  return (1)  
  
    --  Yukon genhistory view  
    select @view_creation_command = 'create view dbo.' + quotename(@genhistoryview_90) + '(guidsrc, pubid, generation,  
            art_nick, nicknames, coldate, genstatus, changecount, subscriber_number) as select DISTINCT guidsrc, CONVERT(uniqueidentifier, '   
            + @guidstr + '), generation, art_nick, nicknames, coldate, genstatus, changecount, subscriber_number from dbo.MSmerge_genhistory gh  
            where genstatus in (1,2) and   
                (art_nick = 0 or art_nick is NULL or art_nick in (select nickname from dbo.sysmergearticles where pubid = ' + @guidstr + ')) and   
                (pubid is null or pubid=(select top 1 pubid from dbo.MSmerge_genhistory where guidsrc=gh.guidsrc))    
            and generation <= ' + convert(nvarchar, @max_bcp_gen)  
  
    if @dynamic_filters = 1  
    begin  
        select @view_creation_command = @view_creation_command + ' and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'  
    end  
  
    exec (@view_creation_command)  
    if @@ERROR <>0  
    begin  
        return (1)  
    end  
    exec @retcode = sys.sp_MS_marksystemobject  @genhistoryview_90   
    if @@ERROR<>0 or @retcode<>0  return (1)  
  
    --  Create the view on MSmerge_genhistory that has the same format as the SQL2000 MSmerge_genhistory  --   
    select @view_creation_command = 'create view dbo.' + quotename(@genhistoryview_80) + ' as   
    select guidsrc, guidlocal = newid(), pubid, generation = isnull(convert(int, generation),0), art_nick, nicknames= {fn REPLNICKARRAY_90_TO_80(nicknames)}, coldate from ' + quotename(@genhistoryview_90)  
  
    exec (@view_creation_command)  
    if @@ERROR <>0   
    begin  
        return (1)  
    end  
    exec @retcode = sys.sp_MS_marksystemobject  @genhistoryview_80   
    if @@ERROR<>0 or @retcode<>0  return (1)  
  
    select @view_creation_command = 'create view dbo.' + quotename(@filtersview_80) + ' as   
        select filtername, join_filterid, pubid, artid, art_nickname, join_articlename,   
                join_nickname, join_unique_key, expand_proc, join_filterclause   
        from dbo.sysmergesubsetfilters where pubid = ' + @guidstr + '   
            and artid in (select artid from dbo.sysmergearticles where pubid = ' + @guidstr + ' and status <> 5 and status <> 6)'  
  
    if @dynamic_filters = 1  
    begin  
        select @view_creation_command = @view_creation_command + ' and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'  
    end  
      
    exec (@view_creation_command)  
    if @@ERROR <>0  
    begin  
        return (1)  
    end  
    exec @retcode = sys.sp_MS_marksystemobject  @filtersview_80   
    if @@ERROR<>0 or @retcode<>0  return (1)  
          
    select @view_creation_command = 'create view dbo.' + quotename(@filtersview_90) + ' as   
        select filtername, join_filterid, pubid, artid, art_nickname, join_articlename,   
                join_nickname, join_unique_key, expand_proc, join_filterclause, filter_type   
        from dbo.sysmergesubsetfilters where pubid = ' + @guidstr + '   
            and artid in (select artid from dbo.sysmergearticles where pubid = ' + @guidstr + ' and status <> 5 and status <> 6)'  
  
    if @dynamic_filters = 1  
    begin  
        select @view_creation_command = @view_creation_command + ' and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'  
    end  
      
    exec (@view_creation_command)  
    if @@ERROR <>0  
    begin  
        return (1)  
    end  
    exec @retcode = sys.sp_MS_marksystemobject  @filtersview_90  
    if @@ERROR<>0 or @retcode<>0  return (1)  
  
    if 1=@need_ctsview_rowtrack  
    begin  
        --  Create the view on MSmerge_contents for lightweight subscribers.  
        select @view_creation_command = 'create view dbo.' + quotename(@rowtrackview) + ' as   
          
        select   
            tablenick,   
            rowguid,   
            changetype=cast(11 as tinyint), --  @METADATA_TYPE_UpsertLightweightProcessed  
            changed=cast(sys.fn_MSdayasnumber(getdate()) as int),  
            rowvector=substring(lineage, 1, 10) + 0xFF,  
            changedcolumns=cast(null as varbinary),  
            columns_enumeration=cast((case sys.fn_fIsColTracked(tablenick)   
                                        when 1 then 0 --  @COLUMNS_ENUMERATED_ChangedOnly  
                                        else 2 --  @COLUMNS_ENUMERATED_AllOnOtherReason  
                                     end) as tinyint),  
            sync_cookie=null  
        from ' + quotename(@contentsview_90) + '  
            where tablenick in (select nickname from dbo.sysmergearticles where upload_options<>1 and upload_options<>2)'  
          
        if @dynamic_filters = 1  
        begin  
            select @view_creation_command = @view_creation_command + ' and ({ fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''') } = 1)'  
        end  
          
        exec (@view_creation_command)  
        if @@ERROR <>0 return (1)  
        exec @retcode = sys.sp_MS_marksystemobject  @rowtrackview   
        if @@ERROR<>0 or @retcode<>0  return (1)  
    end  
  
        --  set security for dynamic filters      
    if @dynamic_filters = 1  
    begin  
  
        set @command = 'grant select on ' + quotename(@contentsview_80) + ' to public'  
        exec(@command)  
        if @@error<>0 return(1)  
        set @command = 'grant select on ' + quotename(@contentsview_90) + ' to public'  
        exec(@command)  
        if @@error<>0 return(1)  
        set @command = 'grant select on ' + quotename(@contentsview_90_forall) + ' to public'  
        exec(@command)  
        if @@error<>0 return(1)  
        if 1=@need_ctsview_forglobal  
        begin  
            set @command = 'grant select on ' + quotename(@contentsview_90_forglobal) + ' to public'  
            exec(@command)  
            if @@error<>0 return(1)  
        end  
        set @command = 'grant select on ' + quotename(@tombstoneview) + ' to public'  
        exec(@command)  
        if @@error<>0 return(1)  
        set @command = 'grant select on ' + quotename(@genhistoryview_80) + ' to public'  
        exec(@command)  
        if @@error<>0 return(1)  
        set @command = 'grant select on ' + quotename(@genhistoryview_90) + ' to public'  
        exec(@command)  
        if @@error<>0 return(1)  
        set @command = 'grant select on ' + quotename(@filtersview_80) + ' to public'          
        exec(@command)  
        if @@error<>0 return(1)  
        set @command = 'grant select on ' + quotename(@filtersview_90) + ' to public'             
        exec(@command)  
        if @@error<>0 return(1)  
        if 1=@need_ctsview_rowtrack  
        begin  
            set @command = 'grant select on ' + quotename(@rowtrackview) + ' to public'  
            exec(@command)  
            if @@error<>0 return(1)  
        end  
    end  
  
Finish:  
    set nocount on  
    --  we only generate per-article contents view for static publications  --   
    exec @retcode = sys.sp_MSgettablecontents @pubid, @dynamic_snapshot_views_table_name, @need_ctsview_rowtrack, @create_dynamic_views, @max_bcp_gen  
    if @@ERROR<>0 OR @retcode <>0 return (1)  
          
    exec('select       
    contentsview,  
    tombstoneview,   
    genhistoryview,   
    filtersview,   
    contentsview_90_forall,   
    contentsview_90_forglobal,   
    genhistoryview_90,   
    filtersview_90,  
    contentsview_90,  
    rowtrackview  
    from #temp_table_for_systable_view   
    order by id')  
      
    drop table #temp_table_for_systable_view  
  
    return (0)