June 8, 2012

sp_schemafilter (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


sys.sp_schemafilter(nvarchar @publisher
, nvarchar @schema
, nvarchar @operation)


-- Name:
-- sp_schemafilter
-- Description:
-- Update the schema filter set used to control those schema
-- whose tables are examined by sp_ORAenumpublishertables (Oracle specific)
-- Inputs:
-- @publisher == name of Oracle publisher
-- @operation == operation ('add', 'drop', 'help')
-- @schema == schema name
-- Returns:
-- Return code (0 for success, 1 for failure)
-- Security:
-- public -- call must be sysadmin
-- Notes:
-- This stored procedure is provided so that the administrator of Oracle
-- publishing can control those schema that are not included when
-- displaying the tables eligible for publishing within the add publication
-- wizard. A default filter set is created when the HREPL package is downloaded
-- to the publisher. This stored procedure allows the entries in the filter set
-- to be both viewed and updated.
-- Operation Action
-- 'add' - Adds the schema specified in @schema to the filter set
-- if not already present.
-- 'drop - Drop the schema specified in @schema from the filter set
-- if present.
-- 'help' - Returns a result set identifying the schema currently
-- in the filter set. If @schema is specified, the result set
-- will be empty if the schema is not in the result set, or
-- will contain the single schema if it was present in the
-- result set. (default)

CREATE PROCEDURE sys.sp_schemafilter
@publisher sysname,
@schema sysname = NULL,
@operation nvarchar(4) = N'help'
DECLARE @cmd nvarchar(4000)
DECLARE @retcode int
DECLARE @publisher_type sysname

-- Security Check: requires sysadmin, done in sp_MSrepl_getpublisherinfo

SET @retcode = 0

EXEC @retcode = sys.sp_MSrepl_getpublisherinfo @publisher = @publisher,
@rpcheader = @cmd OUTPUT,
@publisher_type = @publisher_type OUTPUT,
@hreplOnly = 1

IF @retcode <> 0
RETURN (@retcode)

-- Error if the publisher is not an Oracle publisher
IF @publisher_type NOT LIKE 'ORACLE%'
RAISERROR (21687, 16, -1, @publisher, @publisher_type)

set @cmd = @cmd + N'sys.sp_ORASchemaFilter'

EXEC @retcode = @cmd @publisher,
RETURN (@retcode)

No comments:

Post a Comment

Total Pageviews