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_helpreplicationdboption(nvarchar @dbname, nvarchar @type
, bit @reserved)
MetaData:
-- -- Name: -- sp_helpreplicationdboption -- -- Description: -- This stored procedure shows databases that have the replication -- option enabled. It is executed at the publisher on any database. -- It is used in snapshot, transactional, and merge replication. -- It is not valid for heterogeneous publishers. -- -- When the @dbname is speciified, a result set is returned if the -- database meets the criteria specified in @type. -- -- When the @dbname parameter is NULL, the result set includes entries -- for all databases at the publisher that meet the criteria specified -- in @type. -- -- The user must be a member of the db_owner role of a publishing database -- or in the PAL for a publication associated with the publishing database -- to retrieve information for that database. -- -- Security: -- 'sysadmin', db_owner of publishing database, PAL for publication -- associated with publishing databaseDB -- Requires Certificate signature for catalog access -- -- Returns: -- Result set of database name, database id, transactional publisher, -- merge publisher, current user is dbowner, database is readonly. -- -- Owner: -- <current owner> -- create procedure sys.sp_helpreplicationdboption ( @dbname sysname = N'%' ,@type sysname = N'replication allowed' ,@reserved bit = 0 -- 1 = get publication and pull subscription info ) AS BEGIN SET NOCOUNT ON -- -- Declarations. -- DECLARE @retcode int ,@typebit int ,@distbit int -- bit to distinguish distribution databases ,@dbowner bit ,@issysadmin bit ,@replication_db sysname ,@db_category int ,@inPAL int ,@inPALrole bit ,@transpublish bit ,@mergepublish bit ,@cmd nvarchar(500) SELECT @distbit = 16 -- optimize security check for sysadmin and dbo ,@issysadmin = case when (IS_SRVROLEMEMBER(N'sysadmin') = 1) then 1 else 0 end ,@inPAL = 0 ,@inPALrole = 0 ,@typebit = case when (lower(@type) like N'publish%') then 1 when (lower(@type) like N'subscribe%') then 2 when (lower(@type) like N'merge publish%') then 4 when (lower(@type) like N'merge subscribe%') then 8 when (lower(@type) like N'replication allowed%') then 0 else null end if @typebit is null begin raiserror(14091,-1,-1) return 1 end -- -- Parameter Check: @dbname. -- Check to make sure that the database name conforms to the rules -- for identifiers. -- IF @dbname <> N'%' BEGIN EXECUTE @retcode = sys.sp_validname @dbname IF @@ERROR <> 0 OR @retcode <> 0 RETURN (1) END -- -- create temp table for resultset -- CREATE TABLE #replicationdbs ( name sysname primary key, id int identity NOT NULL, transpublish bit not null, mergepublish bit not null, dbowner bit not null, dbreadonly bit not null, haspublications bit NULL, haspullsubscriptions bit NULL ) -- -- populate the table -- INSERT INTO #replicationdbs (name, transpublish, mergepublish, dbowner, dbreadonly) SELECT name ,is_published ,is_merge_published ,case when (@issysadmin = 1) then 1 else 0 end ,is_read_only FROM master.sys.databases WHERE ((@dbname = N'%') OR (name = @dbname)) AND ((@typebit = 0) or (is_published = 1) or (is_merge_published = 1) or (is_subscribed = 1)) AND (HAS_DBACCESS ( name ) = 1) AND (@typebit != 0 OR is_distributor = 0) -- Filter out distribution databases AND (@typebit != 0 OR name not in (N'master',N'model',N'tempdb',N'msdb',N'MSSQLWeb')) -- filter our system dbs AND source_database_id is NULL -- exclude snapshot files -- -- enumerate database collection -- DECLARE #hCdboinfo CURSOR LOCAL FAST_FORWARD FOR SELECT name, transpublish, mergepublish, dbowner FROM #replicationdbs OPEN #hCdboinfo FETCH #hCdboinfo INTO @replication_db, @transpublish, @mergepublish, @dbowner WHILE (@@fetch_status <> -1) BEGIN -- -- initialize -- select @inPAL = 0 ,@inPALrole = 0 -- -- skip dbowner check when sysadmin -- if (@dbowner = 0) begin exec @dbowner = sys.sp_MSrepl_isdbowner @dbname = @replication_db if (@dbowner = 1) begin update #replicationdbs set dbowner = 1 where name = @replication_db end end -- -- skip PAL check when sysadmin or dbo -- if (@dbowner = 0) begin -- -- Check to see if the user has PAL access to a database publication -- if (@transpublish = 1) begin -- Check PAL for transactional publication select @cmd = quotename(@replication_db) + N'.sys.sp_MScheck_palroleinpubdb ' EXEC @retcode = @cmd @login = NULL ,@hasaccess = @inPAL output end if (@mergepublish = 1) begin -- Check PAL role for merge publication select @cmd = quotename(@replication_db) + N'.sys.sp_MSrepl_IsUserInAnyPAL ' EXEC @retcode = @cmd @raise_error = 0 if @@error = 0 and @retcode = 0 select @inPALrole = 1 end end -- -- Valid entry if user has access -- if (@dbowner = 0 and @inPAL = 0 and @inPALrole = 0) begin DELETE #replicationdbs where name = @replication_db end else if (@reserved = 1) begin -- -- get publication and pull subscription information -- select @cmd = quotename(@replication_db) + N'.sys.sp_MSrepl_helpreplicationdboptionex ' EXEC @retcode = @cmd @name = @replication_db ,@transpublish = @transpublish ,@mergepublish = @mergepublish end -- -- get next db entry -- FETCH #hCdboinfo INTO @replication_db, @transpublish, @mergepublish, @dbowner END CLOSE #hCdboinfo DEALLOCATE #hCdboinfo -- -- return resultset -- if (@reserved = 0) begin SELECT name ,id ,transpublish ,mergepublish ,dbowner ,dbreadonly FROM #replicationdbs order by name end else begin SELECT name ,id ,transpublish ,mergepublish ,dbowner ,dbreadonly ,haspublications ,haspullsubscriptions FROM #replicationdbs order by name end -- all done return 0 END
No comments:
Post a Comment