June 13, 2012

sp_validate_replica_hosts_as_publishers (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_validate_replica_hosts_as_publishers(nvarchar @original_publisher
, nvarchar @publisher_db)

MetaData:

 --   
-- Name: sp_validate_replica_hosts_as_publishers
--
-- Descriptions: Retrieve the redirected publisher for the specified
-- publisher database pair. If the publisher is not
-- currently redirected, return NULL.
--
-- For redirected publishers, a connection is made to the
-- redirected publisher to determine whether the target of
-- redirection is an availability group VNN Name. If it is
-- not, a message is returned indicating that the redirected
-- publisher is not associated with an availability group.
--
-- The availability group primary is then queried for the
-- names of the hosts of the member replicas. Each host in
-- turn is validated as a publisher for the database.
--
-- In general, the stored procedure will attempt to continue
-- after encountering errors, and will try to validate all
-- of the known replica hosts associated with the availability
-- group.
--
-- Parameters: as defined in create statement
--
-- Returns: 0 on success, 1 on failure; on failure errors are raised
--
-- Security: Public procedure invoked via RPC. check caller for db_owner
-- or in the PAL of a publication of the named pubisher check
--
create procedure sys.sp_validate_replica_hosts_as_publishers
(
@original_publisher sysname,
@publisher_db sysname,
@redirected_publisher sysname output
)
as
begin

set nocount on

declare
@target_server sysname,
@publisher_linked_server sysname,
@dbname sysname,
@retcode int,
@use_caller_credentials bit

set @redirected_publisher = null
set @target_server = null
set @publisher_linked_server = null
set @dbname = db_name()
set @use_caller_credentials = 1

-- Has to be executed from a distribution database
--
if (sys.fn_MSrepl_isdistdb (@dbname) <> 1)
begin
raiserror(21874, 16, -1, 'sys.sp_validate_replica_hosts_as_publishers', @dbname)
return 1
end

-- Verify input parameters are not NULL
--
if @original_publisher is null or
@publisher_db is null
begin
raiserror (21875, 16, -1, 'sys.sp_validate_replica_hosts_as_publishers')
return 1
end

-- Security check
--
if is_member(N'db_owner') <> 1
begin
exec @retcode = sys.sp_MSrepl_DistDBPALAccess @original_publisher

if (@retcode <> 0) or (@@error <> 0)
begin
raiserror (21873, 16, -1, 'sys.sp_validate_replica_hosts_as_publishers')
return 1
end
end

-- If the publisher of the database has not been redirected
-- return without performing any validation.
--
select @redirected_publisher = redirected_publisher
from MSredirected_publishers
where upper(original_publisher) = upper(rtrim(@original_publisher))
and publisher_db = rtrim(@publisher_db)

if @redirected_publisher is null
begin
raiserror(21871, 10, 0, @original_publisher, @publisher_db);
return 0
end

-- Verify that the original publisher is a SQL Server publisher of this
-- distributor.
--
if not exists (
select name from msdb.dbo.MSdistpublishers
where upper(rtrim(@original_publisher)) = upper(name) collate database_default
and N'MSSQLSERVER' = upper(publisher_type))
begin
raiserror (21876, 16, -1, @@servername, @original_publisher, @publisher_db)
return 1
end

-- Verify that there is a sysservers entry for the original publisher
-- at the distributor.
--
if not exists (
select srvid
from master..sysservers
where upper(srvname) collate database_default = upper(rtrim(@original_publisher)))
begin
raiserror (21877, 16, -1, @original_publisher, @publisher_db)
return 1
end

-- Create a linked server to connect to the target of redirection.
--
exec @retcode = sys.sp_hadr_create_linked_server @original_publisher, @publisher_db,
@redirected_publisher, @publisher_linked_server output, @use_caller_credentials
if @retcode <> 0
begin
return 1
end

-- Query the availability group primary for the names of the member replicas associated
-- with the publisher database and validate the host server for each replica as a
-- replication publisher.
--
exec @retcode = sys.sp_hadr_validate_replica_hosts_as_publishers @original_publisher,
@redirected_publisher, @publisher_db, @publisher_linked_server

return @retcode
end

sp_vupgrade_mergeobjects (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_vupgrade_mergeobjects(nvarchar @login
, nvarchar @password
, bit @security_mode)

MetaData:

   
create procedure sys.sp_vupgrade_mergeobjects( @login sysname = NULL, @password sysname = N'', @security_mode bit = 1)
as
begin
declare @qual_source_object nvarchar(540),
@artnick int,
@objid int,
@pubid uniqueidentifier,
@artid uniqueidentifier,
@retcode int,
@source_object sysname,
@source_owner sysname

declare @publication_number smallint
declare @partition_id_eval_proc sysname
declare @pubidstr sysname



-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- verify input parameters (1,2.3)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- 1. don't upgrade system databases and distribution databases
if db_name() in (N'master' COLLATE DATABASE_DEFAULT,
N'tempdb' COLLATE DATABASE_DEFAULT,
N'msdb' COLLATE DATABASE_DEFAULT,
N'model' COLLATE DATABASE_DEFAULT)
or sys.fn_MSrepl_isdistdb (db_name()) = 1
or databasepropertyex(db_name(), 'Updateability') <> 'READ_WRITE'
return 1

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

-- 3. Check to ensure a login is provided if security mode is SQL Server authentication.
select @login = rtrim(ltrim(isnull(@login, '')))
if @security_mode = 0 and @login = ''
begin
-- '@login cannot be null or empty when @security_mode is set to 0 (SQL Server authentication).'
raiserror(21694, 16, -1, '@login', '@security_mode')
return 1
end

-- 4. Only upgrade merge databases
if( object_id('dbo.sysmergearticles') is NULL)
return 1

begin tran
save tran vupgrade_mergeobjects

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Loop through each article in the database
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- regenerate procs that is publication-specific ( not on the article leve)
declare @pubs table ( pubid uniqueidentifier) -- a list of publications that has been processed
declare @snapshot_ready tinyint

select @artnick = min(nickname) from dbo.sysmergearticles
while @artnick is not null
begin

select @objid = NULL
select @source_object = NULL
select top 1 @objid = objid, @artid = artid, @pubid = pubid from dbo.sysmergearticles where nickname = @artnick
select @source_owner = schema_name(schema_id), @source_object = name from sys.objects where object_id = @objid
if @objid is NULL or @source_object is NULL
goto error

-- don't regenerate objects if snapshot has not been run
select @snapshot_ready = snapshot_ready from dbo.sysmergepublications where pubid = @pubid
if @snapshot_ready=0
goto nextarticle

-- should we drop trigger before disable triggers?
exec @retcode = sys.sp_MSdroparticletriggers @source_object, @source_owner
if @retcode<>0 or @@error<>0
goto error

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Step 1: disable DML for all articles in this database
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
exec sys.sp_MScreatedisabledmltrigger @source_object, @source_owner
if @retcode<>0 or @@error<>0
goto error

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Step 2: regenerate triggers and procs
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

select @qual_source_object = QUOTENAME(@source_owner) + N'.' + QUOTENAME(@source_object)
exec sys.sp_MSResetTriggerProcs @qual_source_object, @pubid, 1, 1 -- article level reset, regenerate sub procs


-- if this publication has not been processed for regenerating the publication-level objects
if not exists (select pubid from @pubs where pubid = @pubid)
begin
declare @use_partition_groups smallint
select @use_partition_groups = use_partition_groups from dbo.sysmergepublications where pubid = @pubid
if (@use_partition_groups > 0) -- only do this if we use partition groups
begin
exec @retcode = sys.sp_MSsetup_publication_for_partition_groups @pubid

-- regenerate the partitionid_eval proc, but don't change the table, by setting @upgrade = 1
exec @retcode = sys.sp_MSsetup_partition_groups_table @pubid, 1
end

-- now insert this pubid into the tracking table
insert into @pubs(pubid) values( @pubid )
end -- if not exists (select pubid from @pubs where pubid = @pubid)

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Step 3: enable DML for all articles in this database
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

exec sys.sp_MSdropdisabledmltrigger @source_object, @source_owner
if @retcode<>0 or @@error<>0
goto error

nextarticle:
-- find next article
select @artnick = min(nickname) from dbo.sysmergearticles where nickname > @artnick
end -- end article while

commit tran

return (0)

error:
rollback tran vupgrade_mergeobjects
commit tran
return (1)
end

sp_views_rowset2 (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_views_rowset2(nvarchar @view_schema)

MetaData:

   
create procedure sys.sp_views_rowset2
(
@view_schema sysname = null
)
as
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- copy & pasted from version 1 of the SProc and removed checks for 1st parameter !
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
select
TABLE_CATALOG = db_name(),
TABLE_SCHEMA = schema_name(a_v.schema_id),
TABLE_NAME = a_v.name,
VIEW_DEFINITION = convert(nvarchar(1),null),
CHECK_OPTION = convert(bit, NULL),
IS_UPDATABLE = convert(bit, NULL),
DESCRIPTION = convert(nvarchar(1),null),
DATE_CREATED = a_v.create_date,
DATE_MODIFIED = convert(datetime,null)
from
sys.all_views a_v
where
(@view_schema is null or schema_id(@view_schema) = a_v.schema_id) and
has_perms_by_name(quotename(schema_name(a_v.schema_id)) + '.' + quotename(a_v.name),
'object',
'select') = 1
order by 1, 2, 3

sp_views_rowset (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_views_rowset(nvarchar @view_name
, nvarchar @view_schema)

MetaData:

   
create procedure sys.sp_views_rowset
(
@view_name sysname,
@view_schema sysname = null
)
as
select
TABLE_CATALOG = db_name(),
TABLE_SCHEMA = schema_name(a_v.schema_id),
TABLE_NAME = a_v.name,
VIEW_DEFINITION = convert(nvarchar(1),null),
CHECK_OPTION = convert(bit, NULL),
IS_UPDATABLE = convert(bit, NULL),
DESCRIPTION = convert(nvarchar(1),null),
DATE_CREATED = a_v.create_date,
DATE_MODIFIED = convert(datetime,null)
from
sys.all_views a_v
where
(
(@view_schema is null and a_v.name = @view_name) or
object_id(quotename(@view_schema) + '.' + quotename(@view_name)) = a_v.object_id
) and
has_perms_by_name(quotename(schema_name(a_v.schema_id)) + '.' + quotename(@view_name),
'object',
'select') = 1
order by 1, 2, 3

sp_verifypublisher (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_verifypublisher(nvarchar @publisher)

MetaData:

   
--
-- Name:
-- sp_verifypublisher
--
-- Description:
-- Verify that publisher state is valid for HREPL
--
-- Returns:
-- 0 == Valid
-- 1 == Error in publisher state
--
-- Security:
-- Internal
--
-- Notes:
-- Used by any routine that wants to validate
-- the state of the HREPL publisher before
-- performing any actions against it.
-- Errors are issued at warning level to allow
-- the calling proc to decide how to handle it.
-- For example, drop procedures would typically
-- choose to ignore the errors since everything
-- is being dropped. Add procedures would tend
-- to error out immediately.
--

CREATE PROCEDURE sys.sp_verifypublisher
(
@publisher sysname
)
AS
BEGIN
DECLARE @retcode int
DECLARE @publisher_type sysname
DECLARE @cmd nvarchar(4000)

-- -- -- -- security check, db_owner
exec @retcode = dbo.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)

-- Get publisher information
EXEC @retcode = sys.sp_MSrepl_getpublisherinfo @publisher = @publisher,
@publisher_type = @publisher_type OUTPUT,
@rpcheader = @cmd OUTPUT

IF @retcode <> 0
RETURN (@retcode)

-- Execute provider-specific verification
IF UPPER(@publisher_type) IN ('ORACLE', 'ORACLE GATEWAY')
BEGIN
SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT
SELECT @cmd = @cmd + 'sys.sp_ORAverifypublisher'
EXEC @retcode = @cmd @publisher
END
ELSE
BEGIN
RAISERROR(21645, 16, -1, @publisher_type)
SET @retcode = 1
END

return @retcode
END

sp_validname (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_validname(bit @raise_error
, nvarchar @name)

MetaData:

   
-- -- -- -- -- -- -- -- -- -- -- -- -- -- sp_validname -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE PROCEDURE sys.sp_validname
@name sysname,
@raise_error bit = 1
AS
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- Check for valid SQL-Server identifiers: All non-binary-zero
-- characters are valid. String must not be null or empty.
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- QuoteName() will be non-null only if no null-characters. Also check length.
IF (quotename(@name) is not null AND datalength(@name) > 0)
RETURN (0)

-- Bad Name
if @raise_error = 1
raiserror (15004,-1,-1)
return (1) -- sp_validname

sp_validlang (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_validlang(nvarchar @name)

MetaData:

   
-- -- -- -- -- -- -- -- -- -- -- -- -- -- sp_validlang -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE PROCEDURE sys.sp_validlang
@name sysname
AS

-- Check to see if this language is in Syslanguages.
if exists (select * from sys.syslanguages where name = @name or alias = @name)
return(0)

raiserror(15033,-1,-1,@name)
return (1) -- sp_validlang

sp_validatemergesubscription (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_validatemergesubscription(nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, tinyint @level)

MetaData:

   
create procedure sys.sp_validatemergesubscription
(@publication sysname,
@subscriber sysname,
@subscriber_db sysname,
@level tinyint
) AS

set nocount on
declare @retcode int
declare @pubid uniqueidentifier
declare @subid uniqueidentifier
--
-- Security Check
--
EXEC @retcode = sys.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
return (1)

-- make sure current database is enabled for merge replication --
exec @retcode=sys.sp_MSCheckmergereplication
if @@ERROR<>0 or @retcode<>0
return (1)

select @pubid = pubid from dbo.sysmergepublications where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
if @pubid is NULL
begin
raiserror (20026, 16, -1, @publication)
return (1)
end
if @level <1 or @level > 3
begin
raiserror(21184, 16, -1, '@level', '1', '2','3')
return (1)
end

select @subid = NULL
select @subid = subid from dbo.sysmergesubscriptions where pubid=@pubid and db_name=@subscriber_db and
UPPER(subscriber_server) collate database_default = UPPER(@subscriber) collate database_default
if @subid is NULL
begin
raiserror(14055, 16, -1)
return (1)
end


update dbo.MSmerge_replinfo
set validation_level=@level
where repid = @subid
if @@ERROR<>0
return (1)
return (0)

sp_validatemergepullsubscription (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_validatemergepullsubscription(nvarchar @publication
, nvarchar @publisher
, nvarchar @publisher_db
, tinyint @level)

MetaData:

 create procedure sys.sp_validatemergepullsubscription  
(@publication sysname,
@publisher sysname,
@publisher_db sysname,
@level tinyint
) AS

set nocount on
declare @retcode int
declare @pubid uniqueidentifier
declare @subscriber sysname
declare @subscriber_db sysname
declare @subid uniqueidentifier
--
-- Security Check
--
EXEC @retcode = sys.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
return (1)

select @subscriber = @@SERVERNAME, @subscriber_db=db_name()
select @pubid = NULL
select @subid = NULL

select @pubid=pubid from dbo.sysmergepublications where LOWER(publisher)=LOWER(@publisher) and publisher_db=@publisher_db and name=@publication
if @pubid is NULL
begin
raiserror (20026, 16, -1, @publication)
return (1)
end

if @level <0 or @level > 3
begin
raiserror(21184, 16, -1, '@level', '1', '2','3')
return (1)
end

select @subid = subid from dbo.sysmergesubscriptions where pubid=@pubid and db_name=@subscriber_db and LOWER(subscriber_server) = LOWER(@subscriber)
if @subid is NULL
begin
raiserror(14055, 16, -1)
return (1)
end

update dbo.MSmerge_replinfo
set validation_level=@level
where repid = @subid
if @@ERROR<>0
return (1)
return (0)

sp_validatemergepublication (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_validatemergepublication(nvarchar @publication
, tinyint @level)

MetaData:

   
create procedure sys.sp_validatemergepublication
(@publication sysname,
@level tinyint
) AS

set nocount on
declare @artid uniqueidentifier
declare @schematype int
declare @schemaversion int
declare @schemaguid uniqueidentifier
declare @schematext nvarchar
declare @retcode int
declare @pubid uniqueidentifier
declare @compatlevel int

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

-- make sure current database is enabled for merge replication --
exec @retcode=sys.sp_MSCheckmergereplication
if @@ERROR<>0 or @retcode<>0
return (1)

select @pubid = pubid, @compatlevel = backward_comp_level from dbo.sysmergepublications where name = @publication and UPPER(publisher)=UPPER(publishingservername()) and publisher_db=db_name()
if @pubid is NULL
begin
raiserror (20026, 16, -1, @publication)
return (1)
end
if @level <1 or @level > 3
begin
raiserror(21184, 16, -1, '@level', '1', '2','3')
return (1)
end

if @compatlevel < 30
begin
raiserror(21356, 10, -1, @publication)
exec @retcode=sys.sp_MSBumpupCompLevel @pubid, 30
if @@ERROR<>0 or @retcode<>0
return (1)
end

select @schemaversion = schemaversion from dbo.sysmergeschemachange
if (@schemaversion is NULL)
set @schemaversion = 1
else
select @schemaversion = 1 + max(schemaversion) from dbo.sysmergeschemachange
set @schemaguid = newid()
set @artid = newid()
set @schematype = 66 -- publication wide validation --
select @schematext = convert(nvarchar, @level)
exec @retcode=sys.sp_MSinsertschemachange @pubid, @artid, @schemaversion, @schemaguid, @schematype, @schematext
if @@ERROR<>0 or @retcode<>0
return (1)
return (0)

sp_validatelogins (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_validatelogins()

MetaData:

 create procedure sys.sp_validatelogins  
AS
-- Must be securityadmin (or sysadmin) to execute
if is_srvrolemember('securityadmin') = 0 and is_srvrolemember('sysadmin') = 0
begin
raiserror(15247,-1,-1)
return 1
end

-- Use get_sid() to determine if nt name is still valid (builtin is only available from system procs!)
select 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins
where isntname = 1 and get_sid(loginname) is null
return 0 -- sp_validatelogins

sp_validatecache (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_validatecache(nvarchar @publisher
, nvarchar @publication
, nvarchar @article)

MetaData:

   
--
-- Name:
-- sp_validatecache
--
-- Description:
-- Validate the distributor side Oracle meta data (Oracle specific)
--
-- Inputs:
-- @publisher == name of Oracle publisher
-- @publication == publication name (default '%')
-- @article == table name (defulat '%')
--
-- Returns:
-- Return code (0 for success, 1 for failure)
--
-- Result set
--
-- column description
--
-- publication publication name
-- article article name
-- column column name
-- index_or_constraint index or constraint name
-- columnordinal column ordinal
-- type column, index, or constraint type
-- length length
-- prec precision
-- scale scale
-- isnullable is nullable
-- validate primary key validated
-- consstatus constraint status
-- idxstatus index status
-- description 'Column dropped'
-- 'Column added'
-- 'Column changed from'
-- 'Column changed to'
-- 'Index dropped'
-- 'Index added'
-- 'Index changed from'
-- 'Index changed to'
-- 'Constraint dropped'
-- 'Constraint added'
-- 'Constraint changed from'
-- 'Constraint changed to'
-- 'Index column dropped'
-- 'Index column added'
-- 'Constraint column dropped'
-- 'Constraint column added'
--
-- Security:
-- public -- caller must be sysadmin
--
-- Notes:
-- This stored procedure is provided so that the administrator of Oracle
-- publishing can determine whether the current meta data for published
-- Oracle tables that is cached at the distributor, is still valid. It is
-- intended to assist in diagnosing problems resulting from a meta data
-- mismatch between the Oracle database and the distributor meta data cache.
--
-- If the @publication parameter is not specified, all publications for the
-- Oracle publisher are examined. If the @article parameter is not specified,
-- all articles for the given publication are examined. If @article is non NULL,
-- and @publication is NULL, articles of the specified name from all publications
-- are examined.
--
-- If an empty rowset is returned, the distributor meta data is valid.
-- If a non-empty rowset is returned, the distributor meta data has been invalidated
-- by meta data changes at the Oracle publisher.

CREATE PROCEDURE sys.sp_validatecache
(
@publisher sysname,
@publication sysname = '%',
@article sysname = '%'
)
AS
BEGIN
SET NOCOUNT ON

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%'
BEGIN
RAISERROR (21687, 16, -1, @publisher, @publisher_type)
RETURN (1)
END

SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT
set @cmd = @cmd + N'sys.sp_MSrepl_validatecache'

EXEC @retcode = @cmd @publisher,
@publication,
@article
RETURN (@retcode)
END

sp_user_counter8 (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_user_counter8(int @newvalue)

MetaData:

 create procedure sys.sp_user_counter8 @newvalue int as  
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 8', @newvalue)

sp_validate_redirected_publisher (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_validate_redirected_publisher(nvarchar @original_publisher
, nvarchar @publisher_db)

MetaData:

 --   
-- Name: sp_validate_redirected_publisher
--
-- Descriptions: Retrieve the redirected publisher for the specified
-- publisher database pair. If the publisher is not
-- currently redirected, return NULL.
--
-- For redirected publishers, validation checks are performed
-- to verify that the target of the redirection is a suitable
-- host for the published database. Both informational and
-- error messages may be raised.
--
-- NOTE: On error, all called stored procedures will first
-- return the temporary linked server and then raise
-- the error. No errors are caught here.
--
-- Parameters: as defined in create statement
--
-- Returns: 0 on success, 1 on failure; on failure errors are raised
--
-- Security: Public procedure invoked via RPC. check caller for db_owner
-- or in the PAL of a publication of the named pubisher check
--
create procedure sys.sp_validate_redirected_publisher
(
@original_publisher sysname,
@publisher_db sysname,
@redirected_publisher sysname output
)
as
begin

set nocount on

declare
@target_server sysname,
@publisher_linked_server sysname,
@dbname sysname,
@retcode int

set @redirected_publisher = null
set @target_server = null
set @publisher_linked_server = null
set @dbname = db_name()

-- Has to be executed from a distribution database
--
if (sys.fn_MSrepl_isdistdb (@dbname) <> 1)
begin
raiserror(21874, 16, -1, 'sys.sp_validate_redirected_publisher', @dbname)
return 1
end

-- Verify input parameters are not NULL
--
if @original_publisher is null or
@publisher_db is null
begin
raiserror (21875, 16, -1, 'sys.sp_validate_redirected_publisher')
return 1
end

-- Security check
--
if is_member(N'db_owner') <> 1
begin
exec @retcode = sys.sp_MSrepl_DistDBPALAccess @original_publisher

if (@retcode <> 0) or (@@error <> 0)
begin
raiserror (21873, 16, -1, 'sys.sp_validate_redirected_publisher')
return 1
end
end

-- If the publisher of the database has not been redirected
-- return without performing any validation.
--
select @redirected_publisher = redirected_publisher
from MSredirected_publishers
where upper(original_publisher) = upper(rtrim(@original_publisher))
and publisher_db = rtrim(@publisher_db)

if @redirected_publisher is null
begin
return 0
end

-- Verify that the original publisher is a SQL Server publisher of this
-- distributor.
--
if not exists (
select name from msdb.dbo.MSdistpublishers
where upper(rtrim(@original_publisher)) = upper(name) collate database_default
and N'MSSQLSERVER' = upper(publisher_type))
begin
raiserror (21876, 16, -1, @@servername, @original_publisher, @publisher_db)
return 1
end

-- Verify that there is a sysservers entry for the original publisher
-- at the distributor.
--
if not exists (
select srvid
from master..sysservers
where upper(srvname) collate database_default = upper(rtrim(@original_publisher)))
begin
raiserror (21877, 16, -1, @original_publisher, @publisher_db)
return 1
end

-- Create a linked server to connect to the target of redirection.
--
exec @retcode = sys.sp_hadr_create_linked_server @original_publisher, @publisher_db,
@redirected_publisher, @publisher_linked_server output
if @retcode <> 0
return 1

-- Get the name of the target host
--
exec @retcode = sys.sp_hadr_get_target_server @original_publisher, @publisher_db,
@redirected_publisher, @publisher_linked_server, @target_server output
if @retcode <> 0
return 1

-- Verify that the redirected publisher is configured for replication
--
exec @retcode = sys.sp_hadr_verify_configured_for_repl @target_server, @publisher_db,
@publisher_linked_server
if @retcode <> 0
return 1

-- Verify that the original publisher and redirected publisher both share the same
-- distributor
--
exec @retcode = sys.sp_hadr_verify_publisher_at_distributor @target_server, @publisher_db,
@publisher_linked_server
if @retcode <> 0
return 1

-- Verify that the redirected publisher is a replication publisher
--
exec @retcode = sys.sp_hadr_verify_replication_publisher @target_server, @publisher_db,
@publisher_linked_server
if @retcode <> 0
return 1

-- Verify that the published database subscribers of the original publisher are remote
-- servers of the redirected publisher
--
exec @retcode = sys.sp_hadr_verify_subscribers_at_publisher @original_publisher, @target_server,
@publisher_db, @publisher_linked_server
if @retcode <> 0
return 1

-- Drop the temporary linked server without raising an error
--
exec sys.sp_hadr_drop_linked_server @publisher_linked_server

return 0
end

sp_usertypes_rowset2 (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_usertypes_rowset2(nvarchar @type_schema)

MetaData:

   
create procedure sys.sp_usertypes_rowset2
(
@type_schema sysname = null
)
as
select
UDT_CATALOGNAME = db_name(),
UDT_SCHEMANAME = schema_name(u.schema_id),
UDT_NAME = convert(sysname,u.name),
UDT_ASSEMBLY_TYPENAME = u.assembly_qualified_name
from
sys.assemblies a inner join
sys.assembly_types u on
(
a.assembly_id = u.assembly_id
)
where
(@type_schema is null or @type_schema = schema_name(u.schema_id))
order by 1, 2, 3

sp_usertypes_rowset_rmt (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_usertypes_rowset_rmt(nvarchar @type_server
, nvarchar @type_catalog
, nvarchar @type_name
, nvarchar @type_schema
, int @assembly_id)

MetaData:

   
create procedure sys.sp_usertypes_rowset_rmt
(
@type_server sysname,
@type_catalog sysname = null,
@type_name sysname = null,
@type_schema sysname = null,
@assembly_id int = null
)
as
select
UDT_CATALOG_NAME,
UDT_SCHEMA_NAME,
UDT_NAME,
UDT_ASSEMBLY_ID,
MAX_BYTE_SIZE,
IS_FIXED_LENGTH,
IS_BINARY_ORDERED,
BOUND_CLASS_NAME,
PROG_ID
from
-- ISSUE - below pseudo-function is not exposed by metadata code!
sys.fn_remote_usertypes (@type_server,
@type_catalog,
@type_name,
@type_schema,
@assembly_id)
order by 1, 2, 3

sp_usertypes_rowset (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_usertypes_rowset(nvarchar @type_name
, nvarchar @type_schema)

MetaData:

   
create procedure sys.sp_usertypes_rowset
(
@type_name sysname,
@type_schema sysname = null
)
as
select
UDT_CATALOGNAME = db_name(),
UDT_SCHEMANAME = schema_name(u.schema_id),
UDT_NAME = convert(sysname,u.name),
UDT_ASSEMBLY_TYPENAME = u.assembly_qualified_name
from
sys.assemblies a inner join
sys.assembly_types u on
(
a.assembly_id = u.assembly_id
)
where
u.name = @type_name and
(@type_schema is null or @type_schema = schema_name(u.schema_id))
order by 1, 2, 3

sp_user_counter9 (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_user_counter9(int @newvalue)

MetaData:

 create procedure sys.sp_user_counter9 @newvalue int as  
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 9', @newvalue)

sp_user_counter7 (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_user_counter7(int @newvalue)

MetaData:

 create procedure sys.sp_user_counter7 @newvalue int as  
dbcc setinstance ('SQLServer:User Settable', 'Query', 'User counter 7', @newvalue)

Total Pageviews