May 25, 2012

sp_MSrepl_enumpublications (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_MSrepl_enumpublications(bit @reserved)

MetaData:

   
--
-- Name:
-- sp_MSrepl_enumpublications
--
-- Description:
-- Enumerate publications on a database
--
-- Returns:
-- 0 == Failed
-- 1 == Succeed
--
-- Security:
-- public, PAL access for tran publications, dbo check for merge publications
-- Requires Certificate signature for catalog access
--
-- Notes:
-- Used by the UI to generate a list of pubications
--
-- Publication type:
-- 0 == TRAN
-- 1 == SNAPSHOT
-- 2 == MERGE
--
CREATE PROCEDURE sys.sp_MSrepl_enumpublications
(
@reserved bit = 0 -- Set to 1 when used by UI
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @retcode int
,@pubid int
,@pubname sysname
,@username sysname
,@OPT_ENABLED_FOR_P2P int
,@category int
,@skippalcheck bit
,@dbname sysname
,@fpublished bit
,@OPT_ENABLED_FOR_P2PCONFLICTDETECTION int

--
-- initialize
--
select @dbname = db_name()
,@OPT_ENABLED_FOR_P2P = 0x1
,@username = SUSER_SNAME()
,@fpublished = 0
,@OPT_ENABLED_FOR_P2PCONFLICTDETECTION = 0x08
--
-- Verify database is published
-- Have put the code from fn_MSrepl_ispublished inline for faster processing
-- If fn_MSrepl_ispublished() is updated - this code block should be updated as well
--
SELECT @category = category
FROM master.sys.sysdatabases
WHERE name = @dbname

IF (@category IS NOT NULL)
BEGIN
-- We have entry for this db_name in sysdatabases
-- Is this database a distributor?
IF (@category & 16 = 16)
BEGIN
-- DB is a distributor - is it used for HREPL publisher?
IF OBJECT_ID(N'msdb.dbo.MSdistpublishers') IS NOT NULL
BEGIN
IF EXISTS
(
SELECT name
FROM msdb.dbo.MSdistpublishers
WHERE distribution_db = @dbname
AND publisher_type != N'MSSQLSERVER'
)
BEGIN
SELECT @fpublished = 1
END
END
END
ELSE
BEGIN
-- Check if this database is a transactional or merge publisher
IF (@category & 1 = 1 OR @category & 4 = 4)
BEGIN
SELECT @fpublished = 1
END
END
END -- @category not null
--
-- If the database is not published - return
--
IF (@fpublished = 0)
BEGIN
RETURN (0)
END
--
-- Security Check.
-- Skip PAL check if DBO
-- For PAL check - Part of the query to gather the list of publications uses security context
--
select @skippalcheck = case when (is_member ('db_owner') = 1 OR is_srvrolemember('sysadmin') = 1) then 1 else 0 end
if (@skippalcheck = 0)
begin
DECLARE @accessiblepubs TABLE
(
pubid int
)
end
--
-- Create local temp table if needed
--
if (@reserved = 0)
begin
create TABLE #tmp_publications
(
publisher sysname not null,
dbname sysname not null,
publication sysname not null,
publisher_type sysname not null,
publication_type int not null,
description nvarchar(255) null,
allow_queued bit default 0 NOT NULL,
enabled_for_p2p bit default 0 NOT NULL,
enabled_for_p2pconflictdetection bit default 0 NOT NULL
)
end
--
-- Get snapshot or transactional publications
--
IF object_id(N'dbo.syspublications') IS NOT NULL
BEGIN
if (@skippalcheck = 0)
begin
-- Catalog accessible pub ids
DECLARE #hC CURSOR LOCAL FAST_FORWARD FOR
SELECT pubid, name
FROM dbo.syspublications

OPEN #hC
FETCH #hC INTO @pubid, @pubname
WHILE (@@fetch_status <> -1)
BEGIN
EXEC @retcode = sys.sp_MSreplcheck_pull @publication = @pubname,
@raise_fatal_error = 0,
@given_login = @username
IF (@retcode = 0 AND @@error = 0)
BEGIN
INSERT INTO @accessiblepubs values(@pubid)
END

FETCH #hC INTO @pubid, @pubname
END
CLOSE #hC
DEALLOCATE #hC
end -- if (@skippalcheck = 0)
--
-- Determine if distribution db is being cataloged
--
IF (@category & 16 != 16)
BEGIN
-- SQL Server publication db
INSERT INTO #tmp_publications
(
publisher,
dbname,
publication,
publisher_type,
publication_type,
description,
allow_queued,
enabled_for_p2p,
enabled_for_p2pconflictdetection
)
SELECT publishingservername(),
@dbname,
name,
N'MSSQLSERVER',
repl_freq,
description,
allow_queued_tran,
(options & @OPT_ENABLED_FOR_P2P),
(options & @OPT_ENABLED_FOR_P2PCONFLICTDETECTION)
FROM dbo.syspublications
WHERE @skippalcheck = 1
OR (pubid IN (SELECT pubid FROM @accessiblepubs))
END
ELSE
BEGIN
-- Distribution db - Heterogeneous publications
INSERT INTO #tmp_publications
(
publisher,
dbname,
publication,
publisher_type,
publication_type,
description,
allow_queued,
enabled_for_p2p
)
SELECT ss.srvname,
@dbname, -- distribution db name for enumerator to work
sp.name,
msd.publisher_type,
sp.repl_freq,
sp.description,
sp.allow_queued_tran,
(sp.options & @OPT_ENABLED_FOR_P2P)
FROM dbo.syspublications sp
join dbo.MSpublications msp on sp.pubid = msp.publication_id
join master.dbo.sysservers ss on msp.publisher_id = ss.srvid
join msdb.dbo.MSdistpublishers msd on msd.name = ss.srvname
WHERE @skippalcheck = 1
OR (pubid IN (SELECT pubid FROM @accessiblepubs))
END
END -- IF object_id(N'dbo.syspublications') IS NOT NULL
--
-- Get merge publications
--
IF object_id(N'dbo.sysmergepublications') IS NOT NULL
BEGIN
INSERT INTO #tmp_publications
(
publisher,
dbname,
publication,
publisher_type,
publication_type,
description
)
SELECT publisher,
publisher_db,
name,
N'MSSQLSERVER',
2,
description
FROM dbo.sysmergepublications
WHERE (@skippalcheck = 1 OR {fn ISPALUSER(pubid)} = 1)
and publisher_db = @dbname
and UPPER(publisher) = UPPER(publishingservername())
END -- object_id(N'dbo.sysmergepublications') IS NOT NULL
--
-- Return result set if we created local table
--
if (@reserved = 0)
begin
SELECT *
FROM #tmp_publications
ORDER BY publisher, dbname, publication
end
--
-- all done
--
RETURN (0)
END

No comments:

Post a Comment

Total Pageviews