May 14, 2012

sp_MSget_jobstate (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_MSget_jobstate(uniqueidentifier @job_id)

MetaData:

   
-- Procedure sp_MSget_jobstate
--
-- Descriptions:
-- The proc takes a specific Job ID and returns the Job State of the job
-- Returns a row with one column job_state
-- Returns a row with NULL if job does not exist
--
-- Parameters: as defined in create statement
--
-- Returns: 0 - success
-- 1 - Otherwise
--
-- Security:
-- Requires Certificate signature for catalog access
--
create procedure sys.sp_MSget_jobstate
@job_id UNIQUEIDENTIFIER
AS
BEGIN
DECLARE @is_sysadmin INT
DECLARE @job_owner sysname
DECLARE @job_state INT
DECLARE @job_id_as_char VARCHAR(36)

SET NOCOUNT ON

CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

-- Need a job_id
if (@job_id IS NULL)
BEGIN
SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id)
RAISERROR(14262, -1, -1, '@job_id', @job_id_as_char)
RETURN(1) -- Failure
END

-- Capture job execution information (for local jobs only since that's all SQLServerAgent caches)
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = suser_sname(suser_sid())
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

-- Select the job state of the job in question
SELECT @job_state = job_state FROM #xp_results WHERE @job_id = job_id

-- Error if we have no rows selected
if (@job_state IS NULL)
BEGIN
SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id)
RAISERROR(14262, -1, -1, '@job_id', @job_id_as_char)
RETURN(1) -- Failure
END
ELSE
SELECT @job_state

-- All done
DROP TABLE #xp_results
RETURN(0) -- Success
END

sp_MSfillupmissingcols (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_MSfillupmissingcols(nvarchar @publication
, nvarchar @source_table)

MetaData:

   
create procedure sys.sp_MSfillupmissingcols(@publication sysname, @source_table sysname)
AS
declare @sync_objid int
declare @missingcolid int
declare @missing_cols varbinary(128)
declare @excludedcolid int
declare @excludedbm varbinary(128)
declare @excludedcolcnt int
declare @missing_col_count int
declare @maxcolid int
declare @column_tracking bit
declare @id int
declare @pubid uniqueidentifier
declare @missingindex int
declare @retcode int
declare @missing_index_absolute int

-- Security check
exec @retcode= dbo.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0 return (1)

select @id = object_id(@source_table)
select @excludedcolcnt = 0
select @pubid=pubid from dbo.sysmergepublications where name=@publication and publisher=publishingservername() and publisher_db=db_name()
select @sync_objid=sync_objid from dbo.sysmergearticles where objid = @id and pubid=@pubid
select @excludedbm = 0x00
select @missingcolid = 1 -- instead of using the minimal column_id in sys.columns with the same id, as we used to do
select TOP 1 @maxcolid = column_id from sys.columns where object_id=@id order by column_id DESC

-- check if this is a republisher. If it is then pick the missing_cols value from the row for the subscriber.
select @missing_cols = missing_cols,
@missing_col_count = missing_col_count
from dbo.sysmergearticles where objid=@id and pubid<>@pubid and sys.fn_MSmerge_islocalpubid(pubid)=0

if @missing_cols is null
begin
select @missing_cols = missing_cols,
@missing_col_count = missing_col_count
from dbo.sysmergearticles where objid = @id and pubid=@pubid
end

while (@missingcolid <= @maxcolid)
begin
if exists (select * from sys.columns where column_id = @missingcolid and object_id = @id and
is_computed <> 1 and system_type_id <> type_id('timestamp') and
name not in (select name from sys.columns where object_id = @sync_objid))
begin
select @excludedcolcnt = @excludedcolcnt + 1
select @missingindex = count(*) from sys.columns where object_id=@id and column_id<=@missingcolid and is_computed <> 1 and system_type_id <> type_id('timestamp')

-- Get the absolute index of the excluded column by taking into consideration the
-- missing column bitmask.
exec sys.sp_MSget_absolute_colid @missing_cols, @missingindex, @missing_index_absolute OUTPUT

exec sys.sp_MSsetbit @excludedbm OUTPUT, @missing_index_absolute
end
set @missingcolid = @missingcolid + 1
end

UPDATE dbo.sysmergearticles set excluded_col_count = @excludedcolcnt,
excluded_cols = @excludedbm,
missing_col_count = @missing_col_count,
missing_cols = @missing_cols
where objid = @id and pubid=@pubid

sp_MSenumpartialchanges (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_MSenumpartialchanges(int @maxrows
, nvarchar @temp_cont
, int @tablenick
, uniqueidentifier @rowguid
, uniqueidentifier @pubid
, int @compatlevel
, bit @return_count_of_rows_initially_enumerated
, bit @enumentirerowmetadata
, bit @blob_cols_at_the_end
, uniqueidentifier @maxschemaguidforarticle)

MetaData:

 --  This is similiar to sp_MSEnumChanges, except @temp_cont (generated by sp_MSsetupbelongs)   
-- is used instead of MSMerge_Contents plus genlist,maxgen,mingen
-- And @rowguid as input is used as marker
create procedure sys.sp_MSenumpartialchanges
(@maxrows int,
@temp_cont sysname,
@tablenick int,
@rowguid uniqueidentifier,
@pubid uniqueidentifier = NULL,
@compatlevel int = 10, -- backward compatibility level, default=Sphinx
@return_count_of_rows_initially_enumerated bit = 0,
@enumentirerowmetadata bit= 1,
@blob_cols_at_the_end bit=0,
@maxschemaguidforarticle uniqueidentifier=NULL)
as
declare @retcode smallint
declare @tnstring nvarchar(12)
-- Owner qualified
declare @procname nvarchar(270)
declare @error int
declare @count_of_rows_initially_enumerated int

declare @objid int
declare @objid_str nvarchar(15)
declare @sync_objid int
declare @sync_objid_str nvarchar(15)
declare @colid int
declare @rowguid_clause nvarchar(200)

-- Security Checking
-- PAL user has access
exec @retcode = sys.sp_MSrepl_PAL_rolecheck @pubid = @pubid, @tablenick = @tablenick
if (@retcode <> 0) or (@@error <> 0)
return 1

if (@tablenick is null)
begin
RAISERROR(14043, 16, -1, '@tablenick', 'sp_MSenumpartialchanges')
return (1)
end

set @tnstring = convert(nchar, @tablenick)

if @rowguid is null or @rowguid = '00000000-0000-0000-0000-000000000000'
select @rowguid_clause = ' '
else
select @rowguid_clause = ' and rowguid > ''' + convert(nchar(36), @rowguid) + ''' '

create table #cont (
tablenick int NOT NULL,
rowguid uniqueidentifier NOT NULL,
generation bigint NULL,
lineage varbinary(311) NULL,
colv1 varbinary(2953) NULL)

select @procname = 'dbo.' + select_proc, @objid=objid, @sync_objid=sync_objid
from dbo.sysmergearticles
where nickname=@tablenick and pubid = @pubid
if @@rowcount=0
return 1
select @objid_str = convert(nvarchar, @objid)
if @@rowcount=0
return 1
select @sync_objid_str = convert(nvarchar, @sync_objid)
if @@rowcount=0
return 1

declare @selecttop nvarchar(50)
if (@maxrows = 0)
set @selecttop= 'select'
else
set @selecttop= 'select top ' + cast(@maxrows as nvarchar(9))

declare @metadatacols nvarchar(300)

if 0 = @enumentirerowmetadata
set @metadatacols= 'generation, null, null'
else if @compatlevel >= 90
set @metadatacols= 'generation, lineage, colv'
else if sys.fn_fIsColTracked(@tablenick) = 1
begin
declare @cCols int
set @cCols= sys.fn_cColvEntries_80(@pubid, @tablenick)
set @metadatacols= 'generation, {fn LINEAGE_90_TO_80(lineage)}, {fn COLV_90_TO_80(colv,' + cast(@cCols as nvarchar(6)) +')}'
end
else
set @metadatacols= 'generation, {fn LINEAGE_90_TO_80(lineage)}, colv'

execute ('insert into #cont(tablenick, rowguid, generation,lineage,colv1) ' +
@selecttop + ' tablenick, rowguid, ' + @metadatacols + ' from ' + @temp_cont + ' where
tablenick = '
+ @tnstring + @rowguid_clause
+ ' order by rowguid')

select @count_of_rows_initially_enumerated = @@rowcount, @error = @@error
if @error <> 0
begin
return (1)
end

if @return_count_of_rows_initially_enumerated = 1
select @count_of_rows_initially_enumerated

-- Optimize: check colid is valid for the article in this publication or as currently we rely on sel_sp to pick right colid


exec @retcode= @procname
@maxschemaguidforarticle = @maxschemaguidforarticle,
@type= 3, -- for partial changes (horizontal filter)
@enumentirerowmetadata= @enumentirerowmetadata,
@blob_cols_at_the_end=@blob_cols_at_the_end

IF @@ERROR<>0 or @retcode<>0
begin
RETURN (1)
end



drop table #cont
return (0)

sp_MSforce_drop_distribution_jobs (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_MSforce_drop_distribution_jobs(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @type)

MetaData:

 CREATE PROCEDURE sys.sp_MSforce_drop_distribution_jobs  
(
@publisher sysname,
@publisher_db sysname
,@type nvarchar(5) = N'both' -- 'merge' or 'tran' or 'both' to cleanup.
)
AS
BEGIN
DECLARE @retcode int,
@agent_id int,
@publisher_id smallint,
@publication sysname,
@subscriber sysname,
@subscriber_id smallint,
@subscriber_db sysname,
@subscription_type int
,@drop_tran_jobs bit
,@drop_merge_jobs bit

-- Security Check
IF ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0
BEGIN
RAISERROR(21089, 16, -1)
RETURN 1
END

IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) IN (N'tran', N'both')
AND DatabasePropertyEx(@publisher_db, 'IsPublished') = 1
BEGIN
set @drop_tran_jobs = 1
END
else
begin
set @drop_tran_jobs = 0
end

IF LOWER(@type collate SQL_Latin1_General_CP1_CS_AS) IN (N'merge', N'both')
AND DatabasePropertyEx(@publisher_db, 'IsMergePublished') = 1
BEGIN
set @drop_merge_jobs = 1
END
else
BEGIN
set @drop_merge_jobs = 0
END

-- retrieve the publisher_id and if we do not
-- find one then we can just exit silently...
SELECT @publisher_id = server_id
FROM sys.servers
WHERE UPPER(name) = UPPER(@publisher)
IF @publisher_id IS NULL
BEGIN
RETURN 0
END

BEGIN TRANSACTION tr_drop_distribution_jobs
SAVE TRANSACTION tr_drop_distribution_jobs

-- -- -- -- -- -- -- -- -- -- -- -- -
-- DROP QREADER AGENTS
-- -- -- -- -- -- -- -- -- -- -- -- -

-- NOTE:
-- We do not drop qreader agents in the force
-- drop case since we have no way of knowing
-- when we can drop the qreader agent (i.e.
-- when the publisher is using it still...)


-- -- -- -- -- -- -- -- -- -- -- -- -
-- DROP SNAPSHOT AGENTS
-- -- -- -- -- -- -- -- -- -- -- -- -
DECLARE #cursorSnapshot CURSOR LOCAL FAST_FORWARD FOR
SELECT publication
FROM MSsnapshot_agents
WHERE publisher_id = @publisher_id
AND publisher_db = @publisher_db
AND ((@drop_tran_jobs = 1 and publication_type = 1)
or (@drop_merge_jobs = 1 and publication_type = 2)
)
FOR READ ONLY

OPEN #cursorSnapshot

FETCH #cursorSnapshot INTO @publication
WHILE @@FETCH_STATUS != -1
BEGIN
EXEC @retcode = sys.sp_MSdrop_snapshot_agent @publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication
IF @@ERROR != 0 OR @retcode != 0
GOTO UNDO

FETCH #cursorSnapshot INTO @publication
END

CLOSE #cursorSnapshot
DEALLOCATE #cursorSnapshot

if (@drop_tran_jobs = 1)
begin
-- -- -- -- -- -- -- -- -- -- -- -- -
-- DROP LOGREADER AGENTS
-- -- -- -- -- -- -- -- -- -- -- -- -
DECLARE #cursorLogreader CURSOR LOCAL FAST_FORWARD FOR
SELECT publication
FROM MSlogreader_agents
WHERE publisher_id = @publisher_id
AND publisher_db = @publisher_db
FOR READ ONLY

OPEN #cursorLogreader

FETCH #cursorLogreader INTO @publication
WHILE @@FETCH_STATUS != -1
BEGIN
EXEC @retcode = sys.sp_MSdrop_logreader_agent @publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication
IF @@ERROR != 0 OR @retcode != 0
GOTO UNDO

FETCH #cursorLogreader INTO @publication
END

CLOSE #cursorLogreader
DEALLOCATE #cursorLogreader

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- DROP DISTRIBUTION AGENTS
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -
DECLARE #cursorDistrib CURSOR LOCAL FAST_FORWARD FOR
SELECT publication,
subscriber_id,
subscriber_db,
subscription_type
FROM MSdistribution_agents
WHERE publisher_id = @publisher_id
AND publisher_db = @publisher_db
FOR READ ONLY

OPEN #cursorDistrib

FETCH #cursorDistrib INTO @publication, @subscriber_id, @subscriber_db, @subscription_type
WHILE @@FETCH_STATUS != -1
BEGIN
EXEC @retcode = sys.sp_MSdrop_distribution_agent @publisher_id = @publisher_id,
@publisher_db = @publisher_db,
@publication = @publication,
@subscriber_id = @subscriber_id,
@subscriber_db = @subscriber_db,
@subscription_type = @subscription_type
IF @@ERROR != 0 OR @retcode != 0
GOTO UNDO

FETCH #cursorDistrib INTO @publication, @subscriber_id, @subscriber_db, @subscription_type
END

CLOSE #cursorDistrib
DEALLOCATE #cursorDistrib
end
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- DROP MERGE AGENTS
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -
DECLARE #cursorReplmerg CURSOR LOCAL FAST_FORWARD FOR
SELECT msma.publication,
msma.subscriber_name,
msma.subscriber_db
FROM MSmerge_agents msma
WHERE publisher_id = @publisher_id
AND publisher_db = @publisher_db
AND @drop_merge_jobs = 1
FOR READ ONLY

OPEN #cursorReplmerg

FETCH #cursorReplmerg INTO @publication, @subscriber, @subscriber_db
WHILE @@FETCH_STATUS != -1
BEGIN
EXEC @retcode = sys.sp_MSdrop_merge_agent @publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@subscriber = @subscriber,
@subscriber_db = @subscriber_db
IF @@ERROR != 0 OR @retcode != 0
GOTO UNDO

FETCH #cursorReplmerg INTO @publication, @subscriber, @subscriber_db
END

CLOSE #cursorReplmerg
DEALLOCATE #cursorReplmerg

COMMIT TRANSACTION tr_drop_distribution_jobs

RETURN 0
UNDO:
ROLLBACK TRANSACTION tr_drop_distribution_jobs
COMMIT TRANSACTION

RETURN 1
END

sp_MSfast_delete_trans (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_MSfast_delete_trans()

MetaData:

 CREATE PROCEDURE sys.sp_MSfast_delete_trans  
as
begin
declare @retcode int

--
-- security check is done in sp_MSdrop_snapshot_dirs
--
EXEC @retcode = sys.sp_MSdrop_snapshot_dirs
if( @retcode <> 0 or @@error <> 0 )
return 1

truncate table MSrepl_commands
truncate table MSrepl_transactions
end

sp_MSgenerateexpandproc (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_MSgenerateexpandproc(int @tablenick
, nvarchar @procname)

MetaData:

 create procedure sys.sp_MSgenerateexpandproc(  
@tablenick int, @procname sysname
)
AS
begin
set nocount on

declare @immediate_child_partition_viewname nvarchar(130), @immediate_child_nickname int,
@join_unique_key int, @child_expand_proc sysname, @child_rgcol nvarchar(270), @child_has_col_tracking int

declare @partition_view_id int, @partition_view_name nvarchar(270), @pubid uniqueidentifier, @artid uniqueidentifier,
@retcode int, @artidstr nvarchar(40), @rgcol nvarchar(270), @objid int, @publication_number smallint
declare @command1 nvarchar(max), @command2 nvarchar(max), @command3 nvarchar(max), @command4 nvarchar(max)
declare @table_name nvarchar(130), @owner_qualified_table_name nvarchar(270), @immediate_child_table_name nvarchar(130),
@owner_qualified_immediate_child_table_name nvarchar(270), @immediate_child_objid int,
@join_filterclause nvarchar(2000), @child_join_colname nvarchar(130), @child_cannot_pre_exist bit, @parent_columns_unique bit,
@join_filter_id int
declare @gen_change_threshold int
declare @replnick binary(6)


-- Security Check
exec @retcode= sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0 return (1)

select @parent_columns_unique = 0

create table #tmpproccmd (phase int NOT NULL, step int identity NOT NULL, cmdtext nvarchar(max) collate database_default null)

select top 1 @artid=artid, @objid=objid
from dbo.sysmergearticles
where nickname = @tablenick

select @rgcol = quotename(name) from sys.columns where object_id = @objid and is_rowguidcol = 1

select @table_name = quotename(object_name(@objid)),
@owner_qualified_table_name = quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(object_name(@objid))
from sys.objects
where object_id = @objid

select @gen_change_threshold = min(isnull(generation_leveling_threshold,0)) from dbo.sysmergepublications
where pubid in (select pubid from dbo.sysmergearticles where nickname = @tablenick)
select @command1 = '
create procedure dbo.'
+ quotename(@procname) + ' (@marker uniqueidentifier, @inherit_pastchanges_generation bigint, @parent_being_updated bit = 0, @trigger_type int = 1) as '

insert into #tmpproccmd (phase, cmdtext) values (1, @command1)

declare immediate_children CURSOR LOCAL FAST_FORWARD FOR
select distinct quotename(object_name(sma.objid)), sma.objid, sma.nickname, 'dbo.' + quotename(object_name(sma.partition_view_id)),
ssf.join_filterid, ssf.join_filterclause, ssf.join_unique_key, sma.expand_proc, sma.pubid, sma.column_tracking
from dbo.sysmergesubsetfilters ssf, dbo.sysmergepartitioninfoview sma where ssf.join_nickname = @tablenick
and sma.nickname = ssf.art_nickname
and sma.pubid = ssf.pubid
and (ssf.filter_type & 1) = 1
and sma.partition_view_id is not null
for read only

open immediate_children

fetch next from immediate_children into @immediate_child_table_name, @immediate_child_objid, @immediate_child_nickname,
@immediate_child_partition_viewname, @join_filter_id, @join_filterclause, @join_unique_key, @child_expand_proc, @pubid, @child_has_col_tracking

if (@@fetch_status <> -1)
begin
exec @retcode= sys.sp_MSgetreplnick @replnick = @replnick out
if @retcode <> 0
return 1

select @command3 = '
declare @child_marker uniqueidentifier
declare @child_rowcount int, @child_newgen bigint, @child_oldmaxversion int, @child_metadatarows_updated int, @cv varbinary(1), @replnick binary(6)
declare @lineage varbinary(311)
declare @retcode int
declare @dt datetime
declare @nickbin varbinary(8)
declare @reason bit


select @replnick = '
+ sys.fn_varbintohexstr(@replnick) + '

select @nickbin = @replnick + 0xFF

set @child_marker = newid()

select @dt = getdate()
'

insert into #tmpproccmd (phase, cmdtext) values(14, @command3)
end

while (@@fetch_status <> -1)
begin
-- exec @retcode = sys.sp_MSis_joinfilter_based_on_PK_UQ_constraints @pubid = @pubid,
-- @join_filter_id = @join_filter_id,
-- @dri_based = @child_cannot_pre_exist output,
-- @unique_constraint_based = @parent_columns_unique output

-- if @@error <> 0 or @retcode <> 0
-- goto FAILURE

-- if @child_cannot_pre_exist = 1
-- begin
-- select @command3 = '
-- if @parent_being_updated = 1
-- begin '

-- insert into #tmpproccmd (phase, cmdtext) values(14, @command3)
-- end

select @owner_qualified_immediate_child_table_name =
quotename(SCHEMA_NAME(schema_id)) + '.' + quotename(object_name(object_id))
from sys.objects with (nolock) where object_id = @immediate_child_objid

select top 1 @publication_number = publication_number from dbo.sysmergepublications where pubid = @pubid

select top 1 @child_join_colname = name from sys.columns where object_id = @immediate_child_objid
and sys.fn_MSisfilteredcolumn(@join_filterclause, name, @immediate_child_objid) = 1

select @child_rgcol = quotename(name) from sys.columns where object_id = @immediate_child_objid and is_rowguidcol = 1

if @join_unique_key = 0 or @parent_columns_unique = 0
begin
select @command3 = '
select @child_rowcount = count(*) from '
+
@owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ @child_rgcol + ' in
(select '
+ @immediate_child_table_name + '.' + @child_rgcol + '
from
dbo.MSmerge_contents with (rowlock), '

+ @owner_qualified_table_name + ' ' + @table_name + ' with (rowlock), '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where dbo.MSmerge_contents.marker = @marker
and dbo.MSmerge_contents.tablenick = '
+ convert(nvarchar, @tablenick) + '
and dbo.MSmerge_contents.rowguid = '
+ @table_name + '.' + @rgcol + '
and ('
+ @join_filterclause +'))'
end
else
begin
select @command3 = '
select @child_rowcount = count(*) from
dbo.MSmerge_contents with (rowlock), '

+ @owner_qualified_table_name + ' ' + @table_name + ' with (rowlock), '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where dbo.MSmerge_contents.marker = @marker
and dbo.MSmerge_contents.tablenick = '
+ convert(nvarchar, @tablenick) + '
and dbo.MSmerge_contents.rowguid = '
+ @table_name + '.' + @rgcol + '
and ('
+ @join_filterclause + ') '
end

insert into #tmpproccmd (phase, cmdtext) values(14, @command3)

-- touch the immediate children of deleted rows
select @command3 = '
if @child_rowcount > 0
begin
select @child_oldmaxversion= maxversion_at_cleanup from dbo.sysmergearticles
where nickname = '
+ convert(nvarchar, @immediate_child_nickname) + '

-- the code below will get an open generation for the child article
select @child_newgen = NULL
select top 1 @child_newgen = generation from dbo.MSmerge_genhistory with (rowlock, updlock, readpast)
where art_nick = '
+ convert(nvarchar,@immediate_child_nickname) + '
and genstatus = 0'

if @gen_change_threshold > 0
select @command3 = @command3 + '
and changecount <= ('
+ convert(nvarchar, @gen_change_threshold) + '- isnull(@child_rowcount,0))'
select @command3 = @command3 + '
if @child_newgen is NULL
begin
insert into dbo.MSmerge_genhistory with (rowlock)
(guidsrc, genstatus, art_nick, nicknames, coldate, changecount)
values (newid(), 0, '
+ convert(nvarchar,@immediate_child_nickname) + ', @nickbin, @dt, @child_rowcount)
select @child_newgen = @@identity
end'

if @gen_change_threshold > 0
select @command3 = @command3 + '
else
begin
-- do the update right away to change the changecount to include the rows that we just put in the generation
update dbo.MSmerge_genhistory with (rowlock)
set changecount = changecount + @child_rowcount
where generation = @child_newgen
end
'

insert into #tmpproccmd (phase, cmdtext) values(14, @command3)

select @command3 = '
if @retcode <> 0
return @retcode '


insert into #tmpproccmd (phase, cmdtext) values(14, @command3)

if @join_unique_key = 0 or @parent_columns_unique = 0
begin
select @command3 = '
update MSmerge_contents1 with (rowlock) set generation = @child_newgen, partchangegen = @child_newgen, marker = @child_marker,
lineage = case when @trigger_type = 0 then lineage else { fn UPDATELINEAGE(MSmerge_contents1.lineage, @replnick, @child_oldmaxversion+1) } end
from dbo.MSmerge_contents MSmerge_contents1 with (rowlock)
where MSmerge_contents1.tablenick = '
+ convert (nvarchar, @immediate_child_nickname) + '
and MSmerge_contents1.rowguid in
(select '
+ @immediate_child_table_name + '.' + @child_rgcol + '
from
dbo.MSmerge_contents MSmerge_contents2 with (rowlock), '

+ @owner_qualified_table_name + ' ' + @table_name + ' with (rowlock), '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where MSmerge_contents2.marker = @marker
and MSmerge_contents2.tablenick = '
+ convert(nvarchar, @tablenick) + '
and MSmerge_contents2.rowguid = '
+ @table_name + '.' + @rgcol + '
and ('
+ @join_filterclause + '))

select @child_metadatarows_updated = @@rowcount'

end
else
begin
select @command3 = '
update MSmerge_contents1 set generation = @child_newgen, partchangegen = @child_newgen, marker = @child_marker,
lineage = case when @trigger_type = 0 then lineage else { fn UPDATELINEAGE(MSmerge_contents1.lineage, @replnick, @child_oldmaxversion+1) } end
from
dbo.MSmerge_contents MSmerge_contents2 with (rowlock), '

+ @owner_qualified_table_name + ' ' + @table_name + ' with (rowlock), '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock),
dbo.MSmerge_contents MSmerge_contents1 with (rowlock)
where MSmerge_contents1.tablenick = '
+ convert (nvarchar, @immediate_child_nickname) + '
and MSmerge_contents1.rowguid = '
+ @immediate_child_table_name + '.' + @child_rgcol + '
and MSmerge_contents2.marker = @marker
and MSmerge_contents2.tablenick = '
+ convert(nvarchar, @tablenick) + '
and MSmerge_contents2.rowguid = '
+ @table_name + '.' + @rgcol + '
and ('
+ @join_filterclause + ')

select @child_metadatarows_updated = @@rowcount'

end

insert into #tmpproccmd (phase, cmdtext) values(14, @command3)

select @command3 = '
if @child_metadatarows_updated < @child_rowcount '

insert into #tmpproccmd (phase, cmdtext) values(14, @command3)

set @command3 = '
begin
set @lineage = case when @trigger_type = 0 then 0x0 else { fn UPDATELINEAGE(0x0, @replnick, @child_oldmaxversion+1) } end '


insert into #tmpproccmd (phase, cmdtext) values(14, @command3)

if @child_has_col_tracking = 1
set @command3 = '
set @cv = 0xFF '

else
set @command3 = '
set @cv = NULL '


set @command3 = @command3 + '
insert into dbo.MSmerge_contents with (rowlock)
(tablenick, rowguid, generation, partchangegen, lineage, colv1, marker)
'

insert into #tmpproccmd (phase, cmdtext) values(14, @command3)

if @join_unique_key = 0 or @parent_columns_unique = 0
begin
set @command3 = '
select '
+ convert(nvarchar, @immediate_child_nickname) + ', ' + @immediate_child_table_name + '.' + @child_rgcol + ', @child_newgen, @child_newgen, @lineage, @cv, @child_marker
from '

+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where '
+ @child_rgcol + ' in
(
select '
+ @immediate_child_table_name + '.' + @child_rgcol + '
from dbo.MSmerge_contents MSmerge_contents2 with (rowlock),
'
+ @owner_qualified_table_name + ' ' + @table_name + ' with (rowlock),
'
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where MSmerge_contents2.marker = @marker
and MSmerge_contents2.tablenick = '
+ convert(nvarchar, @tablenick) + '
and MSmerge_contents2.rowguid = '
+ @table_name + '.' + @rgcol + '
and ('
+ @join_filterclause + ')
)
and not exists
(select * from dbo.MSmerge_contents with (rowlock)
where tablenick = '
+ convert(nvarchar, @immediate_child_nickname) +'
and rowguid = '
+ @immediate_child_table_name + '.' + @child_rgcol + '
) '

end
else
begin
set @command3 = '
select '
+ convert(nvarchar, @immediate_child_nickname) + ', ' + @immediate_child_table_name +'.' + @child_rgcol + ', @child_newgen, @child_newgen, @lineage, @cv, @child_marker
from
dbo.MSmerge_contents MSmerge_contents2 with (rowlock), '

+ @owner_qualified_table_name + ' ' + @table_name + ' with (rowlock), '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name + ' with (rowlock)
where MSmerge_contents2.marker = @marker
and MSmerge_contents2.tablenick = '
+ convert(nvarchar, @tablenick) + '
and MSmerge_contents2.rowguid = '
+ @table_name + '.' + @rgcol + '
and ('
+ @join_filterclause + ')
and not exists (select * from dbo.MSmerge_contents with (rowlock)
where tablenick = '
+ convert(nvarchar, @immediate_child_nickname) + '
and rowguid = '
+ @immediate_child_table_name + '.' + @child_rgcol + ') '
end

insert into #tmpproccmd (phase, cmdtext) values(14, @command3)

select @command3 = '
end '

insert into #tmpproccmd (phase, cmdtext) values(14, @command3)

select @command3 = '
if @trigger_type <> 0
begin
delete dbo.MSmerge_current_partition_mappings with (rowlock) from
dbo.MSmerge_contents mc with (rowlock)
join dbo.MSmerge_current_partition_mappings with (rowlock)
on dbo.MSmerge_current_partition_mappings.tablenick = mc.tablenick
and mc.tablenick = '
+ convert(nvarchar(11), @immediate_child_nickname) + '
and dbo.MSmerge_current_partition_mappings.tablenick = '
+ convert(nvarchar(11), @immediate_child_nickname) + '
and dbo.MSmerge_current_partition_mappings.rowguid = mc.rowguid
and dbo.MSmerge_current_partition_mappings.publication_number = '
+ convert(nvarchar, @publication_number) + '
and mc.marker = @child_marker
end '

insert into #tmpproccmd (phase, cmdtext) values(14, @command3)

select @command3 = '
if @trigger_type <> 0
begin
insert into dbo.MSmerge_current_partition_mappings with (rowlock) (publication_number, tablenick, rowguid, partition_id)
select distinct '
+ convert(nvarchar, @publication_number) + ', mc.tablenick, mc.rowguid, v.partition_id
from dbo.MSmerge_contents mc with (rowlock)
JOIN '
+ @immediate_child_partition_viewname + ' v with (rowlock)
ON mc.tablenick = '
+ convert(nvarchar(11), @immediate_child_nickname) + '
and mc.rowguid = v.'
+ @child_rgcol + '
and mc.marker = @child_marker
end
else
begin
insert into dbo.MSmerge_current_partition_mappings with (rowlock) (publication_number, tablenick, rowguid, partition_id)
select distinct '
+ convert(nvarchar, @publication_number) + ', mc.tablenick, mc.rowguid, v.partition_id
from dbo.MSmerge_contents mc with (rowlock)
JOIN '
+ @immediate_child_partition_viewname + ' v with (rowlock)
ON mc.tablenick = '
+ convert(nvarchar(11), @immediate_child_nickname) + '
and mc.rowguid = v.'
+ @child_rgcol + '
and mc.marker = @child_marker
and v.partition_id in (select partition_id from dbo.MSmerge_current_partition_mappings cpm with (rowlock) JOIN
dbo.MSmerge_contents mc2 with (rowlock)
ON cpm.rowguid = mc2.rowguid
and mc2.marker = @marker)
where not exists (select * from MSmerge_current_partition_mappings with (readcommitted, rowlock, readpast) where
publication_number = '
+ convert(nvarchar, @publication_number) + ' and
tablenick = '
+ convert(nvarchar(11), @immediate_child_nickname) + ' and
rowguid = v.'
+ @child_rgcol + ' and
partition_id = v.partition_id)
end '


insert into #tmpproccmd (phase, cmdtext) values(14, @command3)

select @command3 = '
if @inherit_pastchanges_generation <> -1
begin
if @parent_being_updated = 1
set @reason = 0
else
set @reason = 1 -- expanding for delete. --

insert into dbo.MSmerge_past_partition_mappings with (rowlock) (publication_number, tablenick, rowguid, partition_id, generation,reason)
select distinct '
+ convert(nvarchar, @publication_number) + ', ' + convert (nvarchar, @immediate_child_nickname) + ', ' + @immediate_child_table_name + '.' + @child_rgcol + ',
mpcpm.partition_id, @child_newgen, @reason
from
dbo.MSmerge_contents mc with (rowlock) ,
dbo.MSmerge_past_partition_mappings mpcpm with (rowlock) , '

+ @owner_qualified_table_name + ' ' + @table_name + ' with (rowlock) , '
+ @owner_qualified_immediate_child_table_name + ' ' + @immediate_child_table_name +' with (rowlock)
where mc.marker = @marker
and mc.tablenick = '
+ convert(nvarchar, @tablenick) + '
and mc.rowguid = '
+ @table_name + '.' + @rgcol + '
and mpcpm.publication_number = '
+ convert(nvarchar, @publication_number) + '
and mpcpm.generation = @inherit_pastchanges_generation
and mc.tablenick = mpcpm.tablenick
and mpcpm.tablenick = '
+ convert(nvarchar, @tablenick) + '
and mc.rowguid = mpcpm.rowguid
and ('
+ @join_filterclause + ')
end '


insert into #tmpproccmd (phase, cmdtext) values(14, @command3)

if @child_expand_proc is not null and @child_expand_proc != ' '
begin
set @command3 = '
exec dbo.'
+ quotename(@child_expand_proc) + ' @marker = @child_marker, @inherit_pastchanges_generation = @child_newgen, @parent_being_updated = 1, @trigger_type = @trigger_type '
-- use parent_being_updated = 1 even if the originating parent was being inserted or deleted. this value
-- simply indicates that the current parent row was found, and we are updating its metadata.
insert into #tmpproccmd (phase, cmdtext) values(14, @command3)
end

select @command3 = '
end '

insert into #tmpproccmd (phase, cmdtext) values(14, @command3)

-- if @child_cannot_pre_exist = 1
-- begin
-- select @command3 = '
-- end '

-- insert into #tmpproccmd (phase, cmdtext) values(14, @command3)
-- end

fetch next from immediate_children into @immediate_child_table_name, @immediate_child_objid, @immediate_child_nickname,
@immediate_child_partition_viewname, @join_filter_id, @join_filterclause, @join_unique_key, @child_expand_proc, @pubid, @child_has_col_tracking
end

close immediate_children
deallocate immediate_children

select cmdtext from #tmpproccmd where cmdtext is not null order by phase, step
drop table #tmpproccmd

return 0

FAILURE:

close immediate_children
deallocate immediate_children

drop table #tmpproccmd

return 1
end

sp_MSenumsubscriptions (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_MSenumsubscriptions(nvarchar @subscription_type
, nvarchar @publisher
, nvarchar @publisher_db
, bit @reserved)

MetaData:

   
create procedure sys.sp_MSenumsubscriptions
(
@subscription_type nvarchar(5) = N'push',
@publisher sysname = N'%',
@publisher_db sysname = N'%',
@reserved bit = 0 -- not to be documented used by UI and sp_MSenumallsubscriptions
)
AS
begin
set nocount on

declare @dbname sysname
,@category int
,@proc nvarchar(200)
,@retcode int
,@cur_db sysname
,@type_value int
,@subscriptiontype_anon int

-- Security check. If not 'db_owner' return without querying
if is_member('db_owner') <> 1
return(0)
select @subscriptiontype_anon= 2
,@cur_db = db_name()
,@type_value = case
when (LOWER(@subscription_type)=N'push') then 0
when (LOWER(@subscription_type)=N'pull') then 1
when (LOWER(@subscription_type)=N'both') then 2
else 100 end
--
-- If we are being invoked by sp_MSenumallsubscriptions
-- we can skip creation of the temp table, for other cases
-- create the temp table
--
if (@reserved = 0)
begin
create table #tmp_subscriptions (
publisher sysname not null,
publisher_db sysname not null,
publication sysname null,
replication_type int not NULL,
subscription_type int not NULL,
last_updated datetime null,
subscriber_db sysname not null,
update_mode smallint null,
last_sync_status int null,
last_sync_summary sysname null,
last_sync_time datetime null
)
end

if object_id(N'dbo.sysmergesubscriptions') is not NULL
begin
-- return all subscriptions that this database is a subscriber to
-- suppress all subscriptions that originate from this database.
insert into #tmp_subscriptions
select p.publisher
,p.publisher_db
,p.name
,2
,s.subscription_type
,s.last_sync_date
,s.db_name
,cast(NULL as smallint)
,s.last_sync_status
,s.last_sync_summary
,s.last_sync_date
from dbo.sysmergepublications as p
join dbo.sysmergesubscriptions as s
on p.pubid = s.pubid
and s.pubid <> s.subid
and lower(s.subscriber_server) collate database_default = lower(@@servername) collate database_default
where (s.subscription_type=@type_value OR @type_value=2)
and ((@publisher = N'%') or (p.publisher = @publisher))
and ((@publisher_db = N'%') or ( p.publisher_db = @publisher_db))
and s.db_name = @cur_db
and p.pubid not in
(select pubid from dbo.sysmergepublications pubs where
lower(pubs.publisher) = LOWER(publishingservername()) AND
pubs.publisher_db = @cur_db)
end

if object_id(N'dbo.MSreplication_subscriptions') is not NULL
begin
if object_id(N'dbo.MSsubscription_properties') is not NULL and
object_id(N'dbo.MSsubscription_agents') is not NULL
begin
-- update_mode in MSreplication_subscriptions table is not reliable.
insert into #tmp_subscriptions
select s.publisher
,s.publisher_db
,s.publication
,case isnull(p.publication_type,0) when 0 then 0 else 1 end
,s.subscription_type
,s.time
,@cur_db
-- NOTE: For Queued case: we will always return 2/3 for the 4/5 case
-- since we overload update_mode based on queue_type
,case when isnull(a.update_mode,0) = 4 then 2
when isnull(a.update_mode,0) = 5 then 3
else isnull(a.update_mode,0) end
,a.last_sync_status
,a.last_sync_summary
,a.last_sync_time
from dbo.MSreplication_subscriptions s with (NOLOCK)
left outer join dbo.MSsubscription_agents a with (NOLOCK)
on (UPPER(s.publisher) = UPPER(a.publisher) and
s.publisher_db = a.publisher_db and
((s.publication = a.publication and
s.independent_agent = 1 and
a.publication <> N'ALL') or
(a.publication = N'ALL' and s.independent_agent = 0)) and
s.subscription_type = a.subscription_type)
left outer join dbo.MSsubscription_properties p with (NOLOCK)
on (UPPER(s.publisher) = UPPER(p.publisher) and
s.publisher_db = p.publisher_db and
s.publication = p.publication and
-- don't use property table for push.
s.subscription_type <> 0)
where
((@publisher = N'%') OR (UPPER(s.publisher) = UPPER(@publisher))) AND
((@publisher_db = N'%') or ( s.publisher_db = @publisher_db)) and
-- eliminate duplicate entries for agents using multiple subscription streams
s.transaction_timestamp = (SELECT MAX(t.transaction_timestamp) FROM dbo.MSreplication_subscriptions t
WHERE t.publisher = s.publisher
AND t.publisher_db = s.publisher_db
AND t.publication = s.publication) and
((s.subscription_type = 0 and @type_value = 0) or
-- For pull, return both pull and anonymous
(s.subscription_type <> 0 and @type_value = 1) or
@type_value = 2)
end
-- Property table does not exists.
else if object_id(N'dbo.MSsubscription_agents') is not NULL
begin
-- update_mode in MSreplication_subscriptions table is not reliable.
insert into #tmp_subscriptions
select s.publisher
,s.publisher_db
,s.publication
-- Property table does not exists. Say transactional.
,0
,s.subscription_type
,s.time, @cur_db
-- NOTE: For Queued case: we will always return 2/3 for the 4/5 case
-- since we overload update_mode based on queue_type
,case when isnull(a.update_mode,0) = 4 then 2
when isnull(a.update_mode,0) = 5 then 3
else isnull(a.update_mode,0) end
,a.last_sync_status
,a.last_sync_summary
,a.last_sync_time
from dbo.MSreplication_subscriptions s with (NOLOCK)
left outer join dbo.MSsubscription_agents a with (NOLOCK)
on (UPPER(s.publisher) = UPPER(a.publisher) and
s.publisher_db = a.publisher_db and
((s.publication = a.publication and
s.independent_agent = 1 and
a.publication <> N'ALL') or
(a.publication = N'ALL' and s.independent_agent = 0)) and
s.subscription_type = a.subscription_type)
where
((@publisher = N'%') OR (UPPER(s.publisher) = UPPER(@publisher))) AND
((@publisher_db = N'%') or ( s.publisher_db = @publisher_db)) and
-- eliminate duplicate entries for agents using multiple subscription streams
s.transaction_timestamp = (SELECT MAX(t.transaction_timestamp) FROM dbo.MSreplication_subscriptions t
WHERE t.publisher = s.publisher
AND t.publisher_db = s.publisher_db
AND t.publication = s.publication) and
((s.subscription_type = 0 and @type_value = 0) or
-- For pull, return both pull and anonymous
(s.subscription_type <> 0 and @type_value = 1) or
@type_value = 2)
end
-- Agents table does not exists.
else if object_id(N'dbo.MSsubscription_properties') is not NULL
begin
-- update_mode in MSreplication_subscriptions table is not reliable.
insert into #tmp_subscriptions
select s.publisher
,s.publisher_db
,s.publication
,case isnull(p.publication_type,0) when 0 then 0 else 1 end
,s.subscription_type
,s.time
,@cur_db
-- NOTE: For Queued case: we will always return 2/3 for the 4/5 case
-- since we overload update_mode based on queue_type
,case when isnull(s.update_mode,0) = 4 then 2
when isnull(s.update_mode,0) = 5 then 3
else isnull(s.update_mode,0) end
,NULL -- a.last_sync_status,
,NULL -- a.last_sync_summary,
,NULL -- a.last_sync_time
from dbo.MSreplication_subscriptions s with (NOLOCK)
left outer join dbo.MSsubscription_properties p with (NOLOCK)
on (UPPER(s.publisher) = UPPER(p.publisher) and
s.publisher_db = p.publisher_db and
s.publication = p.publication and
-- don't use property table for push.
s.subscription_type <> 0)
where
((@publisher = N'%') OR (UPPER(s.publisher) = UPPER(@publisher))) AND
((@publisher_db = N'%') or ( s.publisher_db = @publisher_db)) and
-- eliminate duplicate entries for agents using multiple subscription streams
s.transaction_timestamp = (SELECT MAX(t.transaction_timestamp) FROM dbo.MSreplication_subscriptions t
WHERE t.publisher = s.publisher
AND t.publisher_db = s.publisher_db
AND t.publication = s.publication) and
((s.subscription_type = 0 and @type_value = 0) or
-- For pull, return both pull and anonymous
(s.subscription_type <> 0 and @type_value = 1) or
@type_value = 2)
end
-- Both table does not exists
else
begin
-- update_mode in MSreplication_subscriptions table is not reliable.
insert into #tmp_subscriptions
select s.publisher
,s.publisher_db
,s.publication
,0
,s.subscription_type
,s.time
,@cur_db
-- NOTE: For Queued case: we will always return 2/3 for the 4/5 case
-- since we overload update_mode based on queue_type
,case when isnull(s.update_mode,0) = 4 then 2
when isnull(s.update_mode,0) = 5 then 3
else isnull(s.update_mode,0) end
,NULL -- a.last_sync_status,
,NULL -- a.last_sync_summary
,NULL -- a.last_sync_time
from dbo.MSreplication_subscriptions s with (NOLOCK)
where
((@publisher = N'%') OR (UPPER(s.publisher) = UPPER(@publisher))) AND
((@publisher_db = N'%') or ( s.publisher_db = @publisher_db)) and
-- eliminate duplicate entries for agents using multiple subscription streams
s.transaction_timestamp = (SELECT MAX(t.transaction_timestamp) FROM dbo.MSreplication_subscriptions t
WHERE t.publisher = s.publisher
AND t.publisher_db = s.publisher_db
AND t.publication = s.publication) and
((s.subscription_type = 0 and @type_value = 0) or
-- For pull, return both pull and anonymous
(s.subscription_type <> 0 and @type_value = 1) or
@type_value = 2)
end
end
--
-- If we are being invoked by sp_MSenumallsubscriptions
-- we can skip select of the temp table, for other cases
-- select from the temp table
--
if (@reserved = 0)
begin
select * from #tmp_subscriptions
end
--
-- all done
--
return (0)
end

Total Pageviews