May 16, 2012

sp_MShelpmergeidentity (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_MShelpmergeidentity(nvarchar @publication)

MetaData:

 create procedure sys.sp_MShelpmergeidentity   
@publication sysname
as
declare @pubid uniqueidentifier
declare @artid uniqueidentifier
declare @nickname int
declare @next_seed bigint
declare @retcode int
declare @tablename sysname
declare @range bigint
declare @threshold int
declare @db_name sysname
declare @tmp_table TABLE (tablename sysname, next_seed bigint, range bigint, threshold int, nickname int)
declare @publishingservername sysname
declare @subid uniqueidentifier

--
-- To public.
--
set nocount on
if (@publication is null)
begin
RAISERROR(14003, 16, -1)
return (1)
end

select @db_name = db_name()
,@publishingservername = publishingservername()

--
-- Security Check.
--
exec @retcode = sys.sp_MSmerge_validate_publication_presence @publication, @db_name, @publishingservername, @pubid output
if @retcode <> 0 or @@error <> 0
return 1


select TOP 1 @artid=artid, @nickname=nickname from dbo.sysmergearticles where pubid=@pubid and identity_support=1 order by nickname ASC
while (@artid is not NULL)
begin
-- we need a local subid to look up the entry in MSmerge_identity_range
select @subid=subid from dbo.MSmerge_identity_range where artid=@artid and is_pub_range=1 and (sys.fn_MSmerge_islocalsubid(subid)=1)

insert @tmp_table (tablename, next_seed, range, threshold, nickname)
select object_name(a.objid), i.max_used, a.range, a.threshold, a.nickname
from dbo.sysmergearticles a
inner join dbo.MSmerge_identity_range i on i.artid=a.artid and i.subid=@subid and i.is_pub_range=1
where a.artid = @artid and a.pubid = @pubid
if @@ERROR<>0
begin
raiserror(21197, 16, -1)
return (1)
end
select @artid = NULL
select TOP 1 @artid=artid, @nickname=nickname from dbo.sysmergearticles
where pubid=@pubid and nickname>@nickname and identity_support=1 order by nickname ASC
end
select t.* from @tmp_table t, dbo.sysmergearticles sma
where t.nickname = sma.nickname
and sma.pubid = @pubid
order by sma.processing_order ASC, sma.nickname ASC

return (0)

sp_MShelpsummarypublication (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_MShelpsummarypublication(nvarchar @oename
, nvarchar @oetype)

MetaData:

   
--
-- Name:
-- sp_MShelpsummarypublication
--
-- Description:
-- Called by Object Explorer to populate the Summary Page
-- for a publication
--
-- Security:
-- Public, PAL access
--
-- Returns:
-- 0 : success
-- 1 : failure
--

CREATE PROCEDURE sys.sp_MShelpsummarypublication
(
@oename nvarchar(260),
@oetype nvarchar(100)
)
AS
BEGIN
DECLARE @cmd nvarchar(1000),
@publication sysname,
@publisher_db sysname,
@publisher_type sysname,
@pos_pubname int,
@pos_pubtype int,
@retcode int

-- Verify oename parameter
IF (@oename is null OR LEN(@oename) = 0)
BEGIN
RAISERROR(7801, 16, -1, @oename)
RETURN 1
END

SET @pos_pubname = PATINDEX(N'%:%', @oename)
SET @pos_pubtype = PATINDEX(N'%(%)', @oename)

SELECT @publication = LEFT(@oename, @pos_pubname - 1),
@publisher_db = CASE WHEN @pos_pubtype > 0
THEN SUBSTRING(@oename, @pos_pubname + 1, @pos_pubtype - @pos_pubname - 1)
ELSE SUBSTRING(@oename, @pos_pubname + 1, LEN(@oename) - @pos_pubname)
END,
@publisher_type = CASE WHEN @pos_pubtype > 0
THEN SUBSTRING(@oename, @pos_pubtype + 1, LEN(@oename) - @pos_pubtype - 1)
ELSE NULL
END

IF (@publication IS NULL OR LEN(@publication) = 0 OR @publisher_db IS NULL OR LEN(@publisher_db) = 0)
BEGIN
RAISERROR(21423, 16, -1, @publication) -- The security check is later, so we want to raise the more generic error
RETURN 1
END

--
-- execute the actual proc in publishing db
-- security check is done inside
--

IF @publisher_type IS NULL OR LEN(@publisher_type) = 0
BEGIN
-- SQL Server publication
SELECT @cmd = QUOTENAME(RTRIM(@publisher_db)) + '.sys.sp_publicationsummary'
EXEC @retcode = @cmd @publication = @publication
END
ELSE
BEGIN
-- HREPL publication
EXEC @retcode = sys.sp_publicationsummary @publication = @publication,
@publisher = @publisher_db
END

RETURN @retcode
END

sp_MShelpsnapshot_agent (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_MShelpsnapshot_agent(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication)

MetaData:

   
--
-- Name:
-- sp_MShelpsnapshot_agent
--
-- Description:
-- Displays the following information on snapshot agent:
-- id
-- name
-- publisher_security_mode
-- publisher_login
-- publisher_password
-- job_id
-- job_login
-- job_password
-- schedule_name
-- frequency_type
-- frequency_interval
-- frequency_subday_type
-- frequency_subday_interval
-- frequency_relative_interval
-- frequency_recurrence_factor
-- active_start_date
-- active_end_date
-- active_start_time
-- active_end_time
--
-- Security:
-- SA
-- Requires Certificate signature for catalog access
--
-- Returns:
-- 0 : success
-- 1 : failure
--

CREATE PROCEDURE sys.sp_MShelpsnapshot_agent
(
@publisher sysname,
@publisher_db sysname,
@publication sysname
)
AS
BEGIN
DECLARE @publisher_id int

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

-- database must be distribution db
IF sys.fn_MSrepl_isdistdb(DB_NAME()) <> 1
BEGIN
RAISERROR (21482, 16, -1, 'sp_MShelpsnapshot_agent', 'distribution')
RETURN (1)
END

SELECT @publisher_id = server_id
FROM sys.servers
WHERE UPPER(name) = UPPER(@publisher)

SELECT mssa.id,
mssa.name,
mssa.publisher_security_mode,
mssa.publisher_login,
N'-- -- -- -- -- ' as [publisher_password],
sjb.job_id,
sc.credential_identity as [job_login],
N'-- -- -- -- -- ' as [job_password],
ss.name as [schedule_name],
ss.freq_type as [frequency_type],
ss.freq_interval as [frequency_interval],
ss.freq_subday_type as [frequency_subday_type],
ss.freq_subday_interval as [frequency_subday_interval],
ss.freq_relative_interval as [frequency_relative_interval],
ss.freq_recurrence_factor as [frequency_recurrence_factor],
ss.active_start_date,
ss.active_end_date,
ss.active_start_time,
ss.active_end_time
FROM MSsnapshot_agents mssa
JOIN msdb..sysjobs sjb
ON convert(uniqueidentifier, mssa.job_id) = sjb.job_id
LEFT JOIN msdb..sysjobsteps sj
ON mssa.job_step_uid = sj.step_uid
LEFT JOIN msdb..sysproxies sp
ON sj.proxy_id = sp.proxy_id
LEFT JOIN sys.credentials sc
ON sp.credential_id = sc.credential_id
LEFT JOIN msdb..sysjobschedules sjs
ON sjs.job_id = sjb.job_id
LEFT JOIN msdb..sysschedules ss
ON ss.schedule_id = sjs.schedule_id
WHERE mssa.publisher_id = @publisher_id
AND mssa.publisher_db = @publisher_db
AND mssa.publication = @publication

RETURN 0
END

sp_MShelpreplicationtriggers (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_MShelpreplicationtriggers(nvarchar @object_name
, nvarchar @object_schema)

MetaData:

 create procedure sys.sp_MShelpreplicationtriggers (  
@object_name sysname,
@object_schema sysname
)
as
begin
set nocount on
declare @retcode int
, @object_id int
, @guidstr nvarchar(100)

set @retcode = 0
set @guidstr = null

exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
return 1

set @object_id = object_id(quotename(@object_schema) + N'.' + quotename(@object_name))

if @object_id is null return 0 -- There is nothing to do here

if object_id(N'dbo.sysarticleupdates', 'U') is not null
begin
select 'trigger_name' = object_name(sync_upd_trig)
from dbo.sysarticleupdates updates
inner join dbo.sysarticles arts -- Can't have synctran triggers on
on updates.artid = arts.artid
where arts.objid = @object_id
end

if object_id(N'dbo.sysmergearticles', 'U') is not null
begin
-- Note: All merge articles of the same source object share
-- the same article id.

select @guidstr = sys.fn_MSguidtostr(artid)
from dbo.sysmergearticles
where objid = @object_id

if @guidstr is not null
begin
declare @ins_trigger_name sysname
, @upd_trigger_name sysname
, @del_trigger_name sysname
, @yukon_ins_trigger_name sysname
, @yukon_upd_trigger_name sysname
, @yukon_del_trigger_name sysname

set @ins_trigger_name = N'ins_' + @guidstr
set @upd_trigger_name = N'upd_' + @guidstr
set @del_trigger_name = N'del_' + @guidstr
set @yukon_ins_trigger_name = N'MSmerge_ins_' + @guidstr
set @yukon_upd_trigger_name = N'MSmerge_upd_' + @guidstr
set @yukon_del_trigger_name = N'MSmerge_del_' + @guidstr

select 'trigger_name' = name
from sys.triggers
where parent_class = 1
and parent_id = @object_id
and name in (@ins_trigger_name
,@upd_trigger_name
,@del_trigger_name
,@yukon_ins_trigger_name
,@yukon_upd_trigger_name
,@yukon_del_trigger_name)

end
end

return @retcode
end

sp_MShelpobjectpublications (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_MShelpobjectpublications(nvarchar @object_name)

MetaData:

 create procedure sys.sp_MShelpobjectpublications (@object_name sysname)  
AS
--
-- Declarations.
--
DECLARE @retcode int
DECLARE @mergepublish_bit smallint
DECLARE @transpublish_bit int

declare @object_id int

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

--
-- Initializations
--
select @mergepublish_bit = 4
select @transpublish_bit = 1
select @object_id = OBJECT_ID(@object_name)

create table #helpobjpubs (
publication sysname collate database_default not null,
reptype int NOT NULL,
article sysname collate database_default not null,
article_type int NULL,
column_tracking int NULL,
article_resolver nvarchar(255) collate database_default null,
identity_support int NULL,
resolver_clsid nvarchar(50) NULL,
resolver_info nvarchar(517) NULL,
verify_resolver_signature int NULL,
allow_interactive_resolver bit NULL,
fast_multicol_updateproc bit NULL,
check_permissions int NULL)
--
-- 1. Return the transactional publications that the table is involved in
--
if (select category & @transpublish_bit from master.dbo.sysdatabases where name = DB_NAME() collate database_default) <> 0
begin
if exists (select * from sysextendedarticlesview a,
syspublications p where a.pubid = p.pubid and a.objid = @object_id)
begin
insert into #helpobjpubs(publication, reptype, article, article_type)
select p.name, 1, a.name, a.type from sysextendedarticlesview a,
syspublications p where a.pubid = p.pubid and a.objid = @object_id
IF @@ERROR <> 0
BEGIN
select @retcode = 1
goto DONE
END
end
end
--
-- 2. Return the merge publications that the table is involved in
--
if object_id('sysmergepublications') is not NULL
begin
if exists (select * from dbo.sysmergeextendedarticlesview a,
dbo.sysmergepublications p where a.pubid = p.pubid and a.objid = @object_id)
begin
insert into #helpobjpubs (publication, reptype, article, article_type, column_tracking, article_resolver,
identity_support, resolver_clsid, resolver_info, verify_resolver_signature, allow_interactive_resolver,
fast_multicol_updateproc, check_permissions)
select p.name, 2, a.name, a.type, a.column_tracking, a.article_resolver, a.identity_support,
a.resolver_clsid, a.resolver_info, a.verify_resolver_signature, a.allow_interactive_resolver,
a.fast_multicol_updateproc, a.check_permissions
from dbo.sysmergeextendedarticlesview a, dbo.sysmergepublications p
where a.pubid = p.pubid and a.objid = @object_id
IF @@ERROR <> 0
BEGIN
select @retcode = 1
goto DONE
END
end
end
select @retcode = 0

DONE:
select * from #helpobjpubs
drop table #helpobjpubs
return (@retcode)

sp_MShelpmergeschemaarticles (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_MShelpmergeschemaarticles(nvarchar @publication)

MetaData:

   
create procedure sys.sp_MShelpmergeschemaarticles
@publication sysname
as
begin
set nocount on

declare @pubid uniqueidentifier
declare @retcode int

if (@publication is null)
begin
raiserror(14003, 16, -1)
return (1)
end

if object_id('MSmerge_contents') is NULL
begin
raiserror(20054, 16, -1)
return (1)
end

--
-- Security Check.
--
exec @retcode = sys.sp_MSmerge_validate_publication_presence @publication, NULL, NULL, @pubid output
if @retcode <> 0 or @@error <> 0
return 1

select a.name, o.name, SCHEMA_NAME(o.schema_id), a.destination_object, a.destination_owner, a.artid, @pubid, a.pre_creation_command, a.status, a.type
from dbo.sysmergeschemaarticles a
inner join sys.objects o
on a.objid = o.object_id
where pubid = @pubid

return 0
end

sp_MShelpmergedynamicsnapshotjob (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_MShelpmergedynamicsnapshotjob(nvarchar @publication
, nvarchar @dynamic_snapshot_jobname
, uniqueidentifier @dynamic_snapshot_jobid)

MetaData:

 --   
-- Name: sp_MShelpmergedynamicsnapshotjob
--
-- Description: This procedure returns a listing of dynamic snapshot jobs.
--
-- Parameters: @publication sysname (optional, default '%'): When @publication
-- is '%', all dynamic snapshot jobs with the matching
-- @dynamic_snapshot_jobid and @dynamic_snapshot_jobname will be
-- returned.
-- @dynamic_snapshot_jobname sysname (optional, default '%'): When
-- @dynamic_snapshot_jobname is '%', all dynamic snapshot jobs that
-- belong to @publication with the matching @dynamic_snapshot_jobid
-- will be returned.
-- @dynamic_snapshot_jobid (optional, default null): When
-- @dynamic_snapshot_jobid is null, all dynamic snapshot jobs
-- that belong to @publication with the matching
-- @dynamic_snapshot_jobname will be returned.
--
-- Notes: If all parameters are left unspecified when this procedure is called,
-- all dynamic snapshot jobs for the current database will be returned.
--
-- Result: id int
-- job_name sysname
-- job_id uniqueidentifier -- job id of the dynamic snapshot job
-- dynamic_filter_login sysname
-- dynamic_filter_hostname sysname
-- dynamic_snapshot_location nvarchar(255)
-- Returns: 0 - succeeded
-- 1 - failed
--
-- Security: Execute permission of this stored procedure is granted to public
-- Requires Certificate signature for catalog access
--
create procedure sys.sp_MShelpmergedynamicsnapshotjob (
@publication sysname = N'%',
@dynamic_snapshot_jobname sysname = N'%',
@dynamic_snapshot_jobid uniqueidentifier = null
)
as
begin
set nocount on
declare @retcode int

exec @retcode = sys.sp_MSreplcheck_publish
if @@error<>0 or @retcode<>0
return 1

if object_id('sysmergepublications') is NULL
begin
return 0
end

select 'id' = j.id,
'job_name' = j.name,
'job_id' = j.job_id,
'suser_sname' = j.dynamic_filter_login,
'host_name' = j.dynamic_filter_hostname,
'dynamic_snapshot_location' = j.dynamic_snapshot_location
from dbo.sysmergepublications p
inner join MSdynamicsnapshotjobs j
on p.pubid = j.pubid
where (p.name = @publication or @publication = N'%')
and (j.name = @dynamic_snapshot_jobname or @dynamic_snapshot_jobname = N'%')
and (j.job_id = @dynamic_snapshot_jobid or @dynamic_snapshot_jobid is null)

if @@error <> 0
return (1)
else
return (0)

end

sp_MShelpmergeconflictcounts (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_MShelpmergeconflictcounts(nvarchar @publication_name
, nvarchar @publisher
, nvarchar @publisher_db
, int @logical_record_conflicts)

MetaData:

   
create procedure sys.sp_MShelpmergeconflictcounts (
@publication_name sysname = '%' ,
@publisher sysname = NULL,
@publisher_db sysname = NULL,
@logical_record_conflicts int = 0)
as
begin

set nocount on

declare @aname sysname
declare @cmd nvarchar(2000)
declare @conflict_table sysname
declare @count integer
declare @pubid uniqueidentifier
declare @pubidstr nvarchar(38)
declare @retcode int

-- Security check
if 1 <> is_member('db_owner') and
(1 <> is_member('replmonitor') or is_member('replmonitor') is null)
begin
RAISERROR (15247, 11, -1)
return (1)
end

if @publisher IS NULL
select @publisher = publishingservername()

if @publisher_db IS NULL
select @publisher_db = db_name()

if @publication_name <> '%'
begin
--
-- Parameter Check: @publication.
-- Make sure that the publication exists.
--
select @pubid = pubid from dbo.sysmergepublications
where name = @publication_name and
LOWER(publisher) = LOWER(@publisher) and
publisher_db = @publisher_db
if @pubid IS NULL
BEGIN
RAISERROR (20026, 16, -1, @publication_name)
RETURN (1)
END
set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''
end

-- make sure current db has merge publishing tables (true on both pub and sub)
if object_id('sysmergearticles') is NULL
begin
raiserror( 18757, 16, -1 )
return(1)
end

-- allow null conflict table name to handle case where there are delete conflicts but no update conflicts
create table #result_list ( article sysname collate database_default, source_object sysname collate database_default, conflict_table sysname collate database_default null, guidcolname sysname collate database_default, centralized_conflicts integer, conflicts_ucount integer, conflicts_dcount integer )
create table #conflict_list ( article_name sysname collate database_default, conflicts_ucount integer, conflicts_dcount integer )
create table #update_list ( article_name sysname collate database_default, conflicts_ucount integer )

-- get delete counts
if ( @publication_name = '%' )
declare hCdcount CURSOR LOCAL FAST_FORWARD for
select distinct a.name
from dbo.MSmerge_conflicts_info d
inner join dbo.sysmergepublications p on p.pubid = d.pubid
inner join dbo.sysmergearticles a on a.pubid = p.pubid and a.nickname = d.tablenick
where d.conflict_type in (4,7,8,12)
else
declare hCdcount CURSOR LOCAL FAST_FORWARD for
select distinct a.name
from dbo.MSmerge_conflicts_info d
inner join dbo.sysmergepublications p on p.pubid = d.pubid
inner join dbo.sysmergearticles a on a.pubid = p.pubid and a.nickname = d.tablenick
where p.pubid = @pubid and d.conflict_type in (4,7,8,12)
open hCdcount
fetch hCdcount into @aname
while ( @@fetch_status <> -1 )
begin
select @cmd = 'select N''' + @aname + ''', 0, count(*)
from dbo.MSmerge_conflicts_info d
inner join dbo.sysmergepublications p on p.pubid = d.pubid
inner join dbo.sysmergearticles a on a.pubid = p.pubid and a.nickname = d.tablenick '


if @logical_record_conflicts = 0
begin
set @cmd = @cmd + 'where d.conflict_type in (4,7,8) and a.name = @aname'
end
else
begin
set @cmd = @cmd + 'where d.conflict_type in (4,7,8,12) and a.name = @aname'
end

if ( @publication_name <> '%' )
select @cmd = @cmd + ' and p.pubid = ' + @pubidstr

insert #conflict_list ( article_name, conflicts_ucount, conflicts_dcount )
exec @retcode= dbo.sp_executesql @cmd, N'@aname sysname', @aname = @aname
if @@error<>0 and @retcode<>0
begin
close hCdcount
deallocate hCdcount
return 1
end

fetch hCdcount into @aname
end
close hCdcount
deallocate hCdcount

-- get update counts
if ( @publication_name = '%' )
declare hCucount CURSOR LOCAL FAST_FORWARD for
select distinct a.name, a.conflict_table
from dbo.sysmergepublications p
inner join dbo.sysmergearticles a on a.pubid = p.pubid
where a.conflict_table is not null
else
declare hCucount CURSOR LOCAL FAST_FORWARD for
select distinct a.name, a.conflict_table
from dbo.sysmergepublications p
inner join dbo.sysmergearticles a on a.pubid = p.pubid
where a.conflict_table is not null
and p.pubid = @pubid
open hCucount
fetch hCucount into @aname, @conflict_table
while ( @@fetch_status <> -1 )
begin
select @cmd = N'select N''' + @aname + N''', count(*) from ' + QUOTENAME( @conflict_table ) + N' ct
inner join MSmerge_conflicts_info m on ct.origin_datasource_id=m.origin_datasource_id and ct.rowguidcol=m.rowguid
inner join dbo.sysmergepublications p on p.pubid = m.pubid '


-- Do not return logical record conflicts unless asked for.
if @logical_record_conflicts = 0
begin
select @cmd = @cmd + '
where m.conflict_type not in (11,12,13,14) '


if ( @publication_name <> '%' )
select @cmd = @cmd + ' and p.name = @publication_name'

end
else
begin
if ( @publication_name <> '%' )
select @cmd = @cmd + ' where p.name = @publication_name'
end

insert #update_list ( article_name, conflicts_ucount )
exec @retcode= dbo.sp_executesql @cmd, N'@publication_name sysname', @publication_name = @publication_name
if @@error<>0 and @retcode<>0
begin
close hCucount
deallocate hCucount
return 1
end

fetch hCucount into @aname, @conflict_table
end
close hCucount
deallocate hCucount

update #conflict_list set conflicts_ucount = isnull( ul.conflicts_ucount, 0 )
from #conflict_list cl
inner join #update_list ul on ul.article_name = cl.article_name

delete #update_list
from #update_list ul
inner join #conflict_list cl on ul.article_name = cl.article_name

insert #conflict_list
select *, 0 from #update_list where conflicts_ucount > 0

drop table #update_list
select @cmd = N'insert into #result_list
select distinct t.article_name, N'
''' + quotename(schema_name( o.schema_id )) + ''.'' + quotename(o.name) + '''',' + ' a.conflict_table, c.name, p.centralized_conflicts, t.conflicts_ucount, t.conflicts_dcount
from #conflict_list t
inner join dbo.sysmergearticles a on a.name = t.article_name
inner join dbo.sysmergepublications p on p.pubid = a.pubid
inner join sys.objects o on o.object_id = a.objid
inner join sys.columns c on c.object_id = o.object_id and c.is_rowguidcol = 1
where (t.conflicts_ucount > 0 or t.conflicts_dcount > 0) and a.conflict_table is NOT NULL and p.name like @publication_name'


exec @retcode= sys.sp_executesql @cmd, N'@publication_name sysname', @publication_name=@publication_name
if @@error<>0 or @retcode<>0 return 1

drop table #conflict_list

select * from #result_list
drop table #result_list

return (0)
end

sp_MShelpmergearticles (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_MShelpmergearticles(nvarchar @publication
, int @compatibility_level
, uniqueidentifier @pubidin)

MetaData:

 create procedure sys.sp_MShelpmergearticles  
@publication sysname,
@compatibility_level int = 7000000, -- backward compatibility level, default=Sphinx
@pubidin uniqueidentifier = NULL
as
declare @pubid uniqueidentifier
declare @artid uniqueidentifier
declare @user_name sysname
declare @guid_col sysname
declare @identity_support int
declare @nickname int
declare @next_seed bigint
declare @objid int
declare @qualname nvarchar(270)
declare @retcode int
declare @tablename sysname
declare @range bigint
declare @threshold int
declare @db_name sysname
declare @has_joins int
declare @article_filter_category int
declare @objid_looper int
declare @indexcol int
declare @rowcount1 int
declare @rowcount2 int
declare @use_partition_groups smallint
declare @maxschemaguidforarticle uniqueidentifier

--
-- To public.
--
set nocount on
if (@publication is null)
begin
RAISERROR(14003, 16, -1)
return (1)
end

if object_id('MSmerge_contents') is NULL
begin
raiserror(20054, 16, -1)
return (1)
end

select @db_name = db_name()

if @pubidin is not NULL
set @pubid = @pubidin
else
select @pubid = pubid from dbo.sysmergepublications
where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=@db_name

-- security check
if (@pubid is null) or {fn ISPALUSER(@pubid)} <> 1
begin
RAISERROR (21423, 11, -1, @publication)
return (1)
end

select @use_partition_groups = use_partition_groups from dbo.sysmergepublications where pubid = @pubid
if @use_partition_groups is null
select @use_partition_groups = 0

if (@use_partition_groups <= 0) or (@compatibility_level < 9000000)
begin
exec @retcode = sys.sp_MShelpmergearticles_nonpartgroups @publication, @compatibility_level, @pubidin
return @retcode
end

-- If the 7.0 merge agent is making this call then we need to make sure that the CLSID of the sp resolver is the old one and not the new one
for an 8.0 merge agent we map CLSIDs from all resolvers it has in common with 9.0
all this is done by calling fn_MSrepl_map_resolver_clsid --

-- don't need compat level assume here that it is only called for 90+ compat level
-- Note: Need to convert clsid in Katmai and above to Yukon as well since they are different. Hotfix#801802
exec sys.sp_MShelpmergeselectarticles @pubid, @compatibility_level

return (0)

sp_MShelplogreader_agent (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_MShelplogreader_agent(nvarchar @publisher
, nvarchar @publisher_db)

MetaData:

 --   
-- Name:
-- sp_MShelplogreader_agent
--
-- Description:
-- Displays the following information on logreader agent:
-- LA id
-- name
-- publisher_security_mode
-- publisher_login
-- publisher_password
-- job_id
-- job_login
-- job_password
--
-- Security:
-- SA
-- Requires Certificate signature for catalog access
--
-- Returns:
-- 0 : success
-- 1 : failure
--

CREATE PROCEDURE sys.sp_MShelplogreader_agent
(
@publisher sysname,
@publisher_db sysname
)
AS
BEGIN
DECLARE @publisher_id int

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

-- database must be distribution db
IF sys.fn_MSrepl_isdistdb(DB_NAME()) <> 1
BEGIN
RAISERROR (21482, 16, -1, 'sp_MShelplogreader_agent', 'distribution')
RETURN (1)
END

SELECT @publisher_id = server_id
FROM sys.servers
WHERE UPPER(name) = UPPER(@publisher)

SELECT msla.id,
msla.name,
msla.publisher_security_mode,
msla.publisher_login,
N'-- -- -- -- -- ' as [publisher_password],
sjb.job_id,
sc.credential_identity as [job_login],
N'-- -- -- -- -- ' as [job_password]
FROM MSlogreader_agents msla
JOIN msdb..sysjobs sjb
ON convert(uniqueidentifier, msla.job_id) = sjb.job_id
LEFT JOIN msdb..sysjobsteps sj
ON msla.job_step_uid = sj.step_uid
LEFT JOIN msdb..sysproxies sp
ON sj.proxy_id = sp.proxy_id
LEFT JOIN sys.credentials sc
ON sp.credential_id = sc.credential_id
WHERE msla.publisher_id = @publisher_id
AND msla.publisher_db = @publisher_db

RETURN 0
END

sp_MShelpindex (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_MShelpindex(nvarchar @tablename
, nvarchar @indexname
, int @flags)

MetaData:

 create procedure sys.sp_MShelpindex  
@tablename nvarchar(517), @indexname nvarchar(258) = null, @flags int = null
as
-- -- @flags added for DaVinci uses. If the bit isn't set, use 6.5 -- --
-- -- sp_MShelpindex '%s', null, 1 -- --




create table #tempID
(
cName nvarchar(132) COLLATE database_default NOT NULL, -- Index name --
cInx1 int NULL, cInx2 int NULL, cInx3 int NULL, cInx4 int NULL, cInx5 int NULL, cInx6 int NULL,
cInx7 int NULL, cInx8 int NULL, cInx9 int NULL, cInx10 int NULL, cInx11 int NULL, cInx12 int NULL,
cInx13 int NULL, cInx14 int NULL, cInx15 int NULL, cInx16 int NULL, -- 1 if DESC --
cC1 int NULL, cC2 int NULL, cC3 int NULL, cC4 int NULL, cC5 int NULL, cC6 int NULL,
cC7 int NULL, cC8 int NULL, cC9 int NULL, cC10 int NULL, cC11 int NULL, cC12 int NULL,
cC13 int NULL, cC14 int NULL, cC15 int NULL, cC16 int NULL -- 1 if Computed column --
)

create table #tempID2
(
cName nvarchar(132) COLLATE database_default NOT NULL, -- Index name --
cInx int NULL, -- Combined info --
cComputed int NULL -- 1 if on computed column(s) --
)

-- @flags is for daVinci --
if (@flags is null)
select @flags = 0

set nocount on
insert #tempID
select i.name,
indexkey_property(object_id(@tablename), i.indid, 1, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 2, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 3, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 4, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 5, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 6, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 7, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 8, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 9, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 10, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 11, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 12, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 13, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 14, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 15, N'isdescending'),
indexkey_property(object_id(@tablename), i.indid, 16, N'isdescending'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 1), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 2), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 3), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 4), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 5), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 6), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 7), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 8), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 9), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 10), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 11), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 12), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 13), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 14), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 15), N'IsComputed'),
columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 16), N'IsComputed')
from dbo.sysindexes i
where id = object_id(@tablename) and i.indid > 0 and i.indid < 255
and (@indexname is null or i.name = @indexname)
and (indexkey_property(object_id(@tablename), i.indid, 1, N'isdescending') is not null)
and (i.name is not null)
order by i.indid

-- Construct the bit --
declare @idx int, @isComputed int
declare @Name nvarchar(132)
declare @Inx_1 int, @Inx_2 int, @Inx_3 int, @Inx_4 int, @Inx_5 int, @Inx_6 int, @Inx_7 int, @Inx_8 int
declare @Inx_9 int, @Inx_10 int, @Inx_11 int, @Inx_12 int, @Inx_13 int, @Inx_14 int, @Inx_15 int, @Inx_16 int
declare @C_1 int, @C_2 int, @C_3 int, @C_4 int, @C_5 int, @C_6 int, @C_7 int, @C_8 int
declare @C_9 int, @C_10 int, @C_11 int, @C_12 int, @C_13 int, @C_14 int, @C_15 int, @C_16 int
declare hC cursor global for select * from #tempID
open hC
fetch next from hC into @Name, @Inx_1, @Inx_2, @Inx_3, @Inx_4, @Inx_5, @Inx_6, @Inx_7, @Inx_8,
@Inx_9, @Inx_10, @Inx_11, @Inx_12, @Inx_13, @Inx_14, @Inx_15, @Inx_16,
@C_1, @C_2, @C_3, @C_4, @C_5, @C_6, @C_7, @C_8,
@C_9, @C_10, @C_11, @C_12, @C_13, @C_14, @C_15, @C_16
while (@@FETCH_STATUS = 0)
begin
-- descending? --
select @idx = 0x0000
select @idx = (case when (@Inx_1 = 1) then @idx | 0x0001 else @idx end), @idx = (case when (@Inx_2 = 1) then @idx | 0x0002 else @idx end), @idx = (case when (@Inx_3 = 1) then @idx | 0x0004 else @idx end), @idx = (case when (@Inx_4 = 1) then @idx | 0x0008 else @idx end), @idx = (case when (@Inx_5 = 1) then @idx | 0x0010 else @idx end), @idx = (case when (@Inx_6 = 1) then @idx | 0x0020 else @idx end), @idx = (case when (@Inx_7 = 1) then @idx | 0x0040 else @idx end), @idx = (case when (@Inx_8 = 1) then @idx | 0x0080 else @idx end),
@idx = (case when (@Inx_9 = 1) then @idx | 0x0100 else @idx end), @idx = (case when (@Inx_10 = 1) then @idx | 0x0200 else @idx end), @idx = (case when (@Inx_11 = 1) then @idx | 0x0400 else @idx end), @idx = (case when (@Inx_12 = 1) then @idx | 0x0800 else @idx end), @idx = (case when (@Inx_13 = 1) then @idx | 0x1000 else @idx end), @idx = (case when (@Inx_14 = 1) then @idx | 0x2000 else @idx end), @idx = (case when (@Inx_15 = 1) then @idx | 0x4000 else @idx end), @idx = (case when (@Inx_16 = 1) then @idx | 0x8000 else @idx end)
select @isComputed = 0
select @isComputed = (case when (@C_1 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_2 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_3 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_4 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_5 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_6 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_7 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_8 = 1) then @isComputed | 1 else @isComputed end),
@isComputed = (case when (@C_9 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_10 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_11 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_12 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_13 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_14 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_15 = 1) then @isComputed | 1 else @isComputed end), @isComputed = (case when (@C_16 = 1) then @isComputed | 1 else @isComputed end)
insert #tempID2 select @Name, @idx, @isComputed
fetch next from hC into @Name, @Inx_1, @Inx_2, @Inx_3, @Inx_4, @Inx_5, @Inx_6, @Inx_7, @Inx_8,
@Inx_9, @Inx_10, @Inx_11, @Inx_12, @Inx_13, @Inx_14, @Inx_15, @Inx_16,
@C_1, @C_2, @C_3, @C_4, @C_5, @C_6, @C_7, @C_8,
@C_9, @C_10, @C_11, @C_12, @C_13, @C_14, @C_15, @C_16
end
close hC
deallocate hC

set nocount off
if (@flags <> 0)
begin
-- daVinci is calling --
select i.name, i.status, i.indid, i.OrigFillFactor,
IndCol1 = index_col(@tablename, i.indid, 1),
IndCol2 = index_col(@tablename, i.indid, 2),
IndCol3 = index_col(@tablename, i.indid, 3),
IndCol4 = index_col(@tablename, i.indid, 4),
IndCol5 = index_col(@tablename, i.indid, 5),
IndCol6 = index_col(@tablename, i.indid, 6),
IndCol7 = index_col(@tablename, i.indid, 7),
IndCol8 = index_col(@tablename, i.indid, 8),
IndCol9 = index_col(@tablename, i.indid, 9),
IndCol10 = index_col(@tablename, i.indid, 10),
IndCol11 = index_col(@tablename, i.indid, 11),
IndCol12 = index_col(@tablename, i.indid, 12),
IndCol13 = index_col(@tablename, i.indid, 13),
IndCol14 = index_col(@tablename, i.indid, 14),
IndCol15 = index_col(@tablename, i.indid, 15),
IndCol16 = index_col(@tablename, i.indid, 16)
, SegName = s.groupname
, FullTextKey = IndexProperty(object_id(@tablename), i.name, N'IsFulltextKey')
, Descending = t.cInx
, Computed = t.cComputed
, IsTable = OBJECTPROPERTY(object_id(@tablename), N'IsTable')
from (dbo.sysindexes i inner join
dbo.sysfilegroups s on
i.groupid = s.groupid ), #tempID2 t
where id = object_id(@tablename) and i.indid > 0 and i.indid < 255 and
(@indexname is null or i.name = @indexname) and
(INDEXPROPERTY(object_id(@tablename), i.name, N'IsStatistics') <> 1) and
(INDEXPROPERTY(object_id(@tablename), i.name, N'IsAutoStatistics') <> 1) and
(INDEXPROPERTY(object_id(@tablename), i.name, N'IsHypothetical') <> 1) and
i.name = t.cName
order by i.indid
end else begin
-- select (case when (i.status & 0x0040) != 0 then substring(i.name, 9, (datalength(i.name)/2)-17) else i.name end), i.status, i.indid, i.OrigFillFactor, --
select i.name, i.status, i.indid, i.OrigFillFactor,
IndCol1 = index_col(@tablename, i.indid, 1),
IndCol2 = index_col(@tablename, i.indid, 2),
IndCol3 = index_col(@tablename, i.indid, 3),
IndCol4 = index_col(@tablename, i.indid, 4),
IndCol5 = index_col(@tablename, i.indid, 5),
IndCol6 = index_col(@tablename, i.indid, 6),
IndCol7 = index_col(@tablename, i.indid, 7),
IndCol8 = index_col(@tablename, i.indid, 8),
IndCol9 = index_col(@tablename, i.indid, 9),
IndCol10 = index_col(@tablename, i.indid, 10),
IndCol11 = index_col(@tablename, i.indid, 11),
IndCol12 = index_col(@tablename, i.indid, 12),
IndCol13 = index_col(@tablename, i.indid, 13),
IndCol14 = index_col(@tablename, i.indid, 14),
IndCol15 = index_col(@tablename, i.indid, 15),
IndCol16 = index_col(@tablename, i.indid, 16)
, SegName = s.groupname
, FullTextKey = IndexProperty(object_id(@tablename), i.name, N'IsFulltextKey')
, Descending = t.cInx
, Computed = t.cComputed
, IsTable = OBJECTPROPERTY(object_id(@tablename), N'IsTable')
from (dbo.sysindexes i inner join
dbo.sysfilegroups s on
i.groupid = s.groupid ), #tempID2 t
where id = object_id(@tablename) and i.indid > 0 and i.indid < 255
and (@indexname is null or i.name = @indexname) and
i.name = t.cName
order by i.indid
-- order by i.name --
end

Total Pageviews