April 30, 2012

sp_helptrigger (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_helptrigger(nvarchar @tabname
, char @triggertype)

MetaData:

 create procedure sys.sp_helptrigger  
@tabname nvarchar(776), -- Table name
@triggertype char(6) = NULL -- Trigger type
as

declare @objid int, -- id of the object --
@dbname sysname

-- Check to see that the object names are local to the current database.
select @dbname = parsename(@tabname,3)
,@triggertype = UPPER (@triggertype collate Latin1_General_CI_AS)
if @dbname is null
select @dbname = db_name()
else if @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end

select @objid = object_id from sys.objects where object_id = object_id(@tabname) and type in ('U', 'V')
if @objid is null
begin
raiserror(15009,-1,-1,@tabname,@dbname)
return(1)
end

-- Check that input type is update, INSERT, DELETE --
if @triggertype is not null and not @triggertype in ('UPDATE', 'INSERT', 'DELETE')
begin
raiserror(15305,-1,-1)
return(1)
end

select
trigger_name = name,
trigger_owner = user_name(ObjectProperty( object_id, 'ownerid')),
isupdate = ObjectProperty( object_id, 'ExecIsUpdateTrigger'),
isdelete = ObjectProperty( object_id, 'ExecIsDeleteTrigger'),
isinsert = ObjectProperty( object_id, 'ExecIsInsertTrigger'),
isafter = ObjectProperty( object_id, 'ExecIsAfterTrigger'),
isinsteadof = ObjectProperty( object_id, 'ExecIsInsteadOfTrigger'),
trigger_schema = schema_name(schema_id)
from sys.objects
where parent_object_id = @objid and type IN ('TR','TA')
AND ( @triggertype is NULL
OR ( @triggertype = 'DELETE' AND ObjectProperty( object_id, 'ExecIsDeleteTrigger') = 1 )
OR ( @triggertype = 'INSERT' AND ObjectProperty( object_id, 'ExecIsInsertTrigger') = 1 )
OR ( @triggertype = 'UPDATE' AND ObjectProperty( object_id, 'ExecIsUpdateTrigger') = 1 ) )

return(0) -- sp_helptrigger

sp_helptracertokenhistory (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_helptracertokenhistory(nvarchar @publication
, int @tracer_id
, nvarchar @publisher
, nvarchar @publisher_db)

MetaData:

 create procedure sys.sp_helptracertokenhistory  
(
@publication sysname,
@tracer_id int,
@publisher sysname = NULL,
@publisher_db sysname = NULL
)
as
begin
declare @retcode int,
@distproc nvarchar(1000),
@distributor sysname,
@distribution_db sysname
--
-- Calling convention is different if we are already at the distdb
--
if sys.fn_MSrepl_isdistdb (db_name()) = 1
begin
-- security check for distributor is performed in the helper proc.

-- if we are at the distributor then publisher and
-- publisher db are required parameters for this proc
if isnull(@publisher, N'') = N''
begin
-- Parameter '%s' cannot be NULL or empty string when this procedure is run from a distribution database.
raiserror(20686, 16, -1, '@publisher', 'distribution')
return 1
end
else if isnull(@publisher_db, N'') = N''
begin
-- Parameter '%s' cannot be NULL or empty string when this procedure is run from a distribution database.
raiserror(20686, 16, -1, '@publisher_db', 'distribution')
return 1
end

exec @retcode = sys.sp_MShelptracertokenhistory @publication = @publication,
@tracer_id = @tracer_id,
@publisher = @publisher,
@publisher_db = @publisher_db

return @retcode
end

--
-- Everything below this line is expected to run on the Publisher
--

-- Publisher Security Check.
exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
return 1

-- check this db is published
if sys.fn_MSrepl_istranpublished(db_name(),1) <> 1
begin
-- "The database is not published."
raiserror (18757, 16, -1)
return 1
end

-- do not allow users to specify pub/pubdb unless at disdb
if @publisher is not NULL
begin
-- Parameter ''%s'' must be NULL when this procedure is not being run from a distribution database.
raiserror(20687, 16, -1, '@publisher', 'distribution')
return 1
end
else if @publisher_db is not NULL
begin
-- Parameter ''%s'' must be NULL when this procedure is not being run from a distribution database.
raiserror(20687, 16, -1, '@publisher_db', 'distribution')
return 1
end

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

exec @retcode = sys.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
@distribdb = @distribution_db OUTPUT,
@publisher = @publisher
if @@error <> 0 or @retcode <> 0 or @distributor is NULL or @distribution_db is NULL
begin
-- "The Distributor has not been installed correctly."
raiserror(20036, 16, -1)
return 1
end

select @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribution_db)) + '.sys.sp_MShelptracertokenhistory'
exec @retcode = @distproc @publication = @publication,
@tracer_id = @tracer_id,
@publisher = @publisher,
@publisher_db = @publisher_db
if @@error <> 0 or @retcode <> 0
begin
return 1
end

return 0
end

sp_helpstats (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_helpstats(nvarchar @objname
, nvarchar @results)

MetaData:

   
create procedure sys.sp_helpstats
@objname nvarchar(776), -- the table to check for statistics
@results nvarchar(5) = 'STATS' -- 'ALL' returns indexes & stats, 'STATS' returns just stats
as
-- PRELIM
set nocount on
declare @objid int, -- the object id of the table
@indid int, -- the index id of an index
@indname sysname,
@keys nvarchar(2078),-- string build index key list, length = (16*max_id_length)+(15*2)
@dbname sysname,
@i int,
@thiskey sysname,
@curs cursor

-- Check to see that the object names are local to the current database.
select @dbname = parsename(@objname,3)
if @dbname is null
select @dbname = db_name()
else if @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end

-- Check to see the the table exists and initialize @objid.
select @objid = object_id(@objname, 'local')
if @objid is NULL
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
If UPPER(@results) <> 'STATS' and UPPER(@results)<> 'ALL'
begin
raiserror(N'Invalid option: %s', 1, 1, @results)
return (1)
end

If UPPER(@results) = 'STATS'
begin
set @curs = cursor local fast_forward READ_ONLY for
select stats_id, name from sys.stats
where object_id = @objid
and IndexProperty(@objid, name, 'IsStatistics') = 1 -- User created & auto-created stats
end
else
begin
set @curs = cursor local fast_forward READ_ONLY for
select stats_id, name from sys.stats
where object_id = @objid -- Indexes, User created & auto-created stats
end

open @curs
fetch @curs into @indid, @indname

-- IF NO STATISTICS, QUIT
if @@fetch_status < 0
begin
deallocate @curs
If UPPER(@results) = 'STATS'
begin
raiserror(15574,-1,-1) -- 'Object does not have any statistics.'
end
else
begin
raiserror(15575,-1,-1) -- 'Object does not have any indexes or statistics.'
end
return (0)
end
-- create temp table
CREATE TABLE #spstattab
(
stats_name sysname collate catalog_default NOT NULL,
stats_keys nvarchar(2078) collate catalog_default NOT NULL
)

-- Now check out each statistics set, figure out its keys and
-- save the info in a temporary table that we'll print out at the end.
while @@fetch_status >= 0
begin
-- Skip columnstore indexes as they provide no stats

if indexproperty(@objid, @indname, 'iscolumnstore') <> 1
begin
-- First we'll figure out what the keys are.

select @keys = index_col(@objname, @indid, 1),
@i = 2, @thiskey = index_col(@objname, @indid, 2)

while (@thiskey is not null )
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@objname, @indid, @i)
end

-- INSERT ROW FOR INDEX
insert into #spstattab values (@indname, @keys)
end

-- Next index
fetch @curs into @indid, @indname
end
deallocate @curs

-- DISPLAY THE RESULTS
select
'statistics_name' = stats_name,
'statistics_keys' = stats_keys
from #spstattab
order by stats_name

return (0) -- sp_helpstats

-- -- -- -- -- -- -- -- -- -- -- - sp_helptext -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

raiserror(15339,-1,-1,'sys.sp_helptext')

sp_linkedservers_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_linkedservers_rowset(nvarchar @srvname)

MetaData:

   
create procedure sys.sp_linkedservers_rowset
(
@srvname sysname
)
as
select
SVR_NAME = s_s.name,
SVR_PRODUCT = s_s.product,
SVR_PROVIDERNAME = s_s.provider,
SVR_DATASOURCE = s_s.data_source,
SVR_PROVIDERSTRING = s_s.provider_string,
SVR_LOCATION = s_s.location,
SVR_CATALOG = s_s.catalog
from
sys.servers s_s
where
s_s.name = @srvname and
-- Don't use s_s.is_linked, because 0 there means old-style linked server, 1 means new-style.
s_s.is_data_access_enabled = 1
order by 1

sp_linkedservers (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_linkedservers()

MetaData:

   
create procedure sys.sp_linkedservers
as
select
SRV_NAME = srv.name,
SRV_PROVIDERNAME = srv.provider,
SRV_PRODUCT = srv.product,
SRV_DATASOURCE = srv.data_source,
SRV_PROVIDERSTRING = srv.provider_string,
SRV_LOCATION = srv.location,
SRV_CAT = srv.catalog
from
sys.servers srv
order by 1

sp_link_publication (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_link_publication(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, int @security_mode
, nvarchar @login
, nvarchar @password
, nvarchar @distributor)

MetaData:

 create procedure sys.sp_link_publication   
(
@publisher sysname, -- publishing server name or linked entry name for publisher
@publisher_db sysname, -- publishing database name
@publication sysname, -- publication name
@security_mode int, -- 0 = standard; 1 = integrated; 2 = static linked server entry
@login sysname = null, -- only needed for standard mode
@password sysname = null,-- only needed for standard mode
@distributor sysname = @publisher
)
as
begin
set nocount on
declare @retcode int
,@publisherlink sysname
,@rpcproc nvarchar(4000)
,@islocalpub bit
,@existingsecuritymode int
,@contextuser sysname
,@fcreateduniquelink bit
,@fcreateduser bit
,@fprepcertonsub bit
,@fgrantcertontarget bit
,@netname sysname
,@certname sysname
,@trigcount int
,@palcheck_retcode int
,@regencmd nvarchar(1000)
,@dbname sysname
,@pubversion varbinary(16)
,@publisherlinkusertodrop sysname

--
-- Security Check
--
exec @retcode = sys.sp_MSreplcheck_subscribe
if @@error <> 0 or @retcode <> 0
return (1)
--
-- Parameter check: @security_mode
--
if @security_mode not in (0,1,2)
begin
raiserror(21055, 16, -1, '@security_mode','sp_link_publication')
return (1)
end
--
-- SQL Authenticated linked entry - login cannot be null
--
if (@security_mode = 0 and @login is null)
begin
raiserror(21055, 16, -1, '@login','@security_mode = 0')
return (1)
end
--
-- Create tables if necessary
--
if object_id('dbo.MSreplication_objects') is null or object_id('dbo.MSsubscription_properties') is null
begin
exec @retcode = sys.sp_MScreate_sub_tables_internal @tran_sub_table = 1
if @@error <> 0 or @retcode <> 0
return 1
-- raiserror(20588, 16, -1)
-- return 1
end

--
-- Call cleanup scripts to remove the old linked server (if exists), related triggers
-- and certificates/logins/users.
--
exec @retcode = sys.sp_unlink_publication_internal
@publisher = @publisher
,@publisher_db = @publisher_db
,@publication = @publication
,@publisherlinkusertodrop = @publisherlinkusertodrop output

if @@ERROR <> 0 or @retcode <> 0
return 1

--
-- We can only drop the user, if there are no synctran triggers
-- due to dependancy issues. We can't drop the triggers at this point
-- since we could run into an issue when the sp errors out before the
-- triggers are created where DML would be allowed on the publication's tables.
-- Thus we only attempt to drop the user when there are no sync triggers.
--
select @trigcount = isnull(count(object_name),0)
from dbo.MSreplication_objects
where upper(publisher) = @publisher
and publisher_db = @publisher_db
and publication = @publication
and object_type = 'T'


if @trigcount = 0 and @publisherlinkusertodrop is not null
BEGIN
exec @retcode = sys.sp_dropuser @name_in_db = @publisherlinkusertodrop
if @@error <> 0 or @retcode <> 0
return (1)
END

--
-- initialize
--
select @publisher = upper(@publisher)
,@distributor = upper(@distributor)
,@password = case when (@password = N'') then null else @password end
,@publisher_db = isnull(@publisher_db, db_name())
,@islocalpub = case when (upper(@publisher) = upper(@@servername)) then 1 else 0 end
,@fcreateduniquelink = 0
,@fprepcertonsub = 0
,@fgrantcertontarget = 0
--
-- Check for an entry in MSsubscription_properties that
-- was created uniquely for this subscription by sp_link_publication
-- should have the prefix as defined
--
select @publisherlink = publisherlink
,@existingsecuritymode = publisher_security_mode
from MSsubscription_properties
where upper(publisher) = @publisher
and publisher_db = @publisher_db
and publication = @publication

--
-- process based on security_mode
--
if @security_mode in (0,1)
begin
--
-- Standard or Integrated
--
if (@islocalpub = 1)
begin
--
-- local publisher - we will not use linked server
-- for security_modes 0 and 1. Set publisher link
-- to servername for reference
--
select @publisherlink = @publisher
if @security_mode = 0
begin
--
-- Special processing for standard security
-- the user for publiser db for given login for SQL authentication
-- will have a proxy user in subscriber db with db_owner privileges
-- get the user in publisher db
--
if (ISNULL(IS_SRVROLEMEMBER('sysadmin'),@login) = 1)
begin
--
-- special case for sysadmin users
-- map contextuser to dbo
--
select @contextuser = 'dbo'
end
else
begin
select @rpcproc =
N'select @p1 = a.name from ' + quotename(@publisher_db)
+ N'.sys.database_principals as a join master.dbo.syslogins as b on a.sid = b.sid where b.loginname = @p2'
exec @retcode = sys.sp_executesql @stmt = @rpcproc
,@params = N'@p1 sysname output, @p2 sysname'
,@p1 = @contextuser output
,@p2 = @login
end
--
-- valid pubdb user should exist
--
if @contextuser is null
begin
raiserror(21855, 16, 1, @login, @publisher_db)
return (1)
end
--
-- for sysadmin logins and the case when the user for the publisher login does not exist on
-- this subscriber db - we will use proxy
-- for other case when user for the publisher login exists on the subscriber db we will use it
--
if (lower(@contextuser) = 'dbo') or
not exists (select * from sys.database_principals as a join master.dbo.syslogins as b
on a.sid = b.sid
where a.name = @contextuser and b.loginname = @login)
begin
--
-- we will use a proxy user on both publisher db and subscriber db to avoid SA escalation
--
exec @retcode = sys.sp_MSprocesspublisherlink @mode = 1, @islocalpub = @islocalpub, @securitymode = @security_mode
if @@error <> 0 or @retcode <> 0
return(1)
--
-- we will also create the proxy user on publishing db
--
select @rpcproc = quotename(@publisher_db) + N'.sys.sp_MSprocesspublisherlink'
exec @retcode = @rpcproc @mode = 1, @islocalpub = @islocalpub, @securitymode = @security_mode
if @@error <> 0 or @retcode <> 0
return(1)
select @contextuser = N'repllinkproxy'
,@login = N'repllinkproxy' -- override user specified local login
,@fcreateduser = 1
end
end -- @security_mode = 0
end -- (@islocalpub = 1)
else
begin -- (@islocalpub = 0)
--
-- Remote publisher - we will create a unique linked server entry for this subscription
-- if there is one existing we will drop and recreate it
--
select @publisherlink = sys.fn_MSrepllinkname(N'REPLLINK', @publisher, @publisher_db, @publication, db_name())
--
-- Refresh the linked entry
--
exec @retcode = sys.sp_MSprocesspublisherlink 1, @islocalpub, @security_mode, @publisherlink, @publisher, @login, @password
if @@error <> 0 or @retcode <> 0
return(1)
--
-- we have predefined user context for SQL authentication
--
select @contextuser = N'repllinkproxy'
,@login = N'repllinkproxy' -- local login will always be this
,@fcreateduniquelink = 1
,@fcreateduser = 1
end -- (@islocalpub = 0)
end
else
begin
--
-- static linked server case
--
if (@existingsecuritymode in (0,1) and @islocalpub = 0)
begin
--
-- we might have a linked entry that was created for standard/integrated mode
-- drop this linked entry
--
select @publisherlink = sys.fn_MSrepllinkname(N'REPLLINK', @publisher, @publisher_db, @publication, db_name())
exec sys.sp_MSprocesspublisherlink 2, @islocalpub, @existingsecuritymode, @publisherlink, @publisher
end
--
-- publisher should exist in sys.servers
--
select @publisherlink = name
,@netname = upper(data_source)
from sys.servers
where upper(name) = @publisher
if (@publisherlink is null)
begin
raiserror(20620, 16, -1, @publisher)
return (1)
end
--
-- Adjust publisher name as necessary
--
if (@netname != @publisher)
begin
select @publisher = @netname
end
end

--
-- Do PAL validation on local or remote publisher db
-- Build a command string that can be executed on the context of
-- the security mode and on a given link if necessary.
-- This also avoids direct usage of REVERT which
-- is not supported on 80 compatible databases
--
select @rpcproc = N'declare @cmd nvarchar(4000) '
+ N'declare @abc table( rc int ) '
+ N'select @cmd = N''declare @retcode int exec @retcode = '
+ sys.fn_replreplacesinglequote(quotename(@publisher_db))
+ N'.dbo.sp_MSreplcheck_pull @publication = N'''''
+ replace(@publication, N'''', N'''''''''')
+ N''''' select @retcode'' '
+ N'insert into @abc(rc) '
+ N'execute (@cmd) '
--
-- for security mode 0, execute in proxy login context
--
if (@security_mode = 0)
begin
select @rpcproc = @rpcproc
+ N'as login = N'''
+ sys.fn_replreplacesinglequote(@login) + N''' '
end
--
-- For remote publisher specify the link where the execution needs to happen
-- This validates the linked entry and ensures
-- proper processing for resource SPs on publisher
--
if (@islocalpub = 0)
begin
select @rpcproc = @rpcproc
+ N'at '
+ quotename(@publisherlink)
+ N' select @palrc = rc from @abc '
end
--
-- execute this command under proper user context
-- and get PAL check return code
--
exec @retcode = sp_executesql
@stmt = @rpcproc
,@params = N'@palrc int output'
,@palrc = @palcheck_retcode output
if @@error <> 0 or @retcode <> 0 or @palcheck_retcode <> 0
goto UNDO


--
-- RPC to get commands needed to regenerate subscriber triggers
--
create table #tab1 ( cmd nvarchar(4000) )
select @dbname = db_name()

--
-- Check to see if initial sync is done, if not skip trigger regeneration
--
declare @num_dup_rows int
,@agent_id int
select @agent_id = avg(id), @num_dup_rows = count(*) from MSsubscription_agents where
UPPER(publisher) = UPPER(@publisher) and
publisher_db = @publisher_db and
-- we know that we have independent agents (enforced in
-- sp_addpublication) so specify the publication name
publication = @publication and
-- We know the subscription must be updateble. This
-- is to reduce the chance of dup rows.
update_mode <> 0

if @agent_id is not null and @num_dup_rows <= 1
begin
create table #ver (version varbinary(16))
declare @cmd nvarchar(500)
select @cmd = N'execute (N''select @@microsoftversion'')'
if (@security_mode = 0)
select @cmd = @cmd + N'as login = N''' + sys.fn_replreplacesinglequote(@login) + N''' '
if (@islocalpub = 0)
select @cmd = @cmd + N' at ' + QUOTENAME(@publisherlink)

insert into #ver exec sp_executesql @stmt=@cmd
select @pubversion = version from #ver
drop table #ver

select @regencmd = ''

if (@islocalpub != 0 or @pubversion > 0x09000577)
begin
if (@islocalpub = 0)
begin
select @regencmd = QUOTENAME(@publisherlink) + N'.'
end

select @regencmd = @regencmd
+ QUOTENAME(@publisher_db)
+ N'.dbo.sp_script_synctran_commands N'''
+ sys.fn_replreplacesinglequote(@publication)
+ N''', N''all'', 1'

if (@security_mode = 0)
insert into #tab1 exec (@regencmd) as login = N'repllinkproxy'
else
insert into #tab1 exec (@regencmd)
end
else -- not possible to fall through on a local case
begin
select @regencmd = QUOTENAME(@publisherlink)
+ N'.' + QUOTENAME(@publisher_db)
+ N'.dbo.sp_script_synctran_commands N'''
+ sys.fn_replreplacesinglequote(@publication)
+ N''', N''all'''

if (@security_mode = 0)
insert into #tab1 exec (@regencmd) as login = N'repllinkproxy'
else
insert into #tab1 exec (@regencmd)

delete from #tab1 WHERE cmd NOT LIKE N'if (@@microsoftversion >= 0x080002C0) begin exec sp_addsynctriggers%' COLLATE database_default

declare @offset_length int
select @offset_length = LEN(N'if (@@microsoftversion >= 0x080002C0) begin exec ') + 2
update #tab1 set cmd = SUBSTRING(cmd, 0, @offset_length) + N'sys.' + SUBSTRING(cmd, @offset_length, LEN(cmd)-@offset_length-3) + N',1 end'
-- tack the extra parameter on the end, and add sys. in front of the SP name
end
end
BEGIN TRAN
--
-- Now store the linked server information in MSsubscription_properties
--
if exists (select * from MSsubscription_properties
where upper(publisher) = @publisher
and publisher_db = @publisher_db
and publication = @publication)
begin
update MSsubscription_properties set
publisher_login = null,
publisher_password = null,
publisher_security_mode = @security_mode,
publisherlink = case when (@security_mode in (0,1)) then N'xxxx' else @publisherlink end,
publisherlinkuser = case when (@security_mode in (0,1) and @contextuser = N'repllinkproxy') then N'xxxx' else @contextuser end,
distributor = isnull(@distributor, distributor)
where upper(publisher) = @publisher
and publisher_db = @publisher_db
and publication = @publication
end
else
begin
insert into MSsubscription_properties (publisher, publisher_db, publication, publication_type,
publisher_security_mode, distributor, distributor_security_mode, publisherlink, publisherlinkuser)
values (@publisher, @publisher_db, @publication, 0,
@security_mode, ISNULL(@distributor, @publisher), 1,
case when (@security_mode in (0,1)) then N'xxxx' else @publisherlink end,
case when (@security_mode in (0,1) and @contextuser = N'repllinkproxy') then N'xxxx' else @contextuser end)
end
--
-- Check for errors in update/insert
--
if @@error <> 0
BEGIN
rollback tran
goto UNDO
END

--
-- recreate trigger based on security mode
-- get the trigger definition
--

exec @retcode = xp_execresultset N'select cmd from #tab1', @dbname
if (@retcode != 0 or @@error <> 0)
begin
rollback tran
goto UNDO
end

drop table #tab1

--
-- get count of triggers
--
select @trigcount = isnull(count(object_name),0)
from dbo.MSreplication_objects
where upper(publisher) = @publisher
and publisher_db = @publisher_db
and publication = @publication
and object_type = 'T'

--
-- certificate signing stage
-- proceed only if we have triggers available
--
if (@security_mode = 0) and (@trigcount >= 3) and (@trigcount % 3 = 0)
begin
--
-- Create a well known certificate on subdb
--
select @certname = N'REPLCERT_' + db_name() + cast(newid() as sysname)
exec @retcode = sys.sp_MSrepltrigpreparecert @mode = 1
,@certname = @certname
,@publisher = @publisher
,@publisher_db = @publisher_db
,@publication = @publication
if @@error != 0 or @retcode != 0
begin
rollback tran
goto UNDO
end
select @fprepcertonsub = 1
--
-- grant privileges necessary
--
if (@islocalpub = 0)
begin
select @rpcproc = N'master.sys.sp_MSrepltrigcertgrant'
exec @retcode = @rpcproc @mode = 1
,@islocalpub = @islocalpub
,@certname = @certname
,@targetdb = N'master'
end
else
begin
select @rpcproc = quotename(@publisher_db) + N'.sys.sp_MSrepltrigcertgrant'
exec @retcode = @rpcproc @mode = 1
,@islocalpub = @islocalpub
,@certname = @certname
,@targetdb = @publisher_db
end
if @@error != 0 or @retcode != 0
begin
rollback tran
goto UNDO
end
select @fgrantcertontarget = 1
end -- if (@security_mode = 0) and (@trigcount >= 3) and (@trigcount % 3 = 0)

commit tran
--
-- all done
--
return 0

UNDO:
--
-- clean up certificates if they are created, granted on target
-- drop the link if it has been created
-- drop local user if created for local publisher
--
if (@fgrantcertontarget = 1)
begin
if (@islocalpub = 0)
begin
select @rpcproc = N'master.sys.sp_MSrepltrigcertgrant'
exec @rpcproc @mode = 2
,@islocalpub = @islocalpub
,@certname = @certname
,@targetdb = N'master'
end
else
begin
select @rpcproc = quotename(@publisher_db) + N'.sys.sp_MSrepltrigcertgrant'
exec @rpcproc @mode = 2
,@islocalpub = @islocalpub
,@certname = @certname
,@targetdb = @publisher_db
end
end
if (@fprepcertonsub = 1)
begin
exec sys.sp_MSrepltrigpreparecert @mode = 2
,@certname = @certname
,@publisher = @publisher
,@publisher_db = @publisher_db
,@publication = @publication
end
if (@fcreateduniquelink = 1)
begin
exec @retcode = sys.sp_MSprocesspublisherlink @mode = 2
,@islocalpub = @islocalpub, @securitymode = @security_mode
,@linkname = @publisherlink, @remserver = @publisher
end
if (@fcreateduser = 1)
begin
exec sys.sp_dropuser @name_in_db = @contextuser
end
--
-- return error
--
return 1
end

sp_lightweightmergemetadataretentioncleanup (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_lightweightmergemetadataretentioncleanup()

MetaData:

 create procedure sys.sp_lightweightmergemetadataretentioncleanup  
@num_rowtrack_rows int = 0 output
as
declare @delbatchsize int
declare @delcount int
declare @retcode smallint

-- security check
exec @retcode = sys.sp_MSreplcheck_subscribe
if @retcode <> 0 or @@error <> 0 return 1

set @delbatchsize= 5000
set @num_rowtrack_rows= 0

set @delcount= @delbatchsize
while @delcount = @delbatchsize
begin
delete top (@delbatchsize) from dbo.MSmerge_rowtrack
where 1=sys.fn_MSrowispastretention(tablenick, changed, getdate())

set @delcount= @@rowcount
set @num_rowtrack_rows= @num_rowtrack_rows + @delcount
end

set @delcount= @delbatchsize
while @delcount = @delbatchsize
begin
delete top (@delbatchsize) from dbo.MSmerge_metadataaction_request
where 1=sys.fn_MSrowispastretention(tablenick, changed, getdate())

set @delcount= @@rowcount
end

sp_kill_filestream_non_transacted_handles (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_kill_filestream_non_transacted_handles(nvarchar @table_name
, int @handle_id)

MetaData:

   
create procedure sys.sp_kill_filestream_non_transacted_handles
(
@table_name nvarchar(776) = NULL,
@handle_id int = -1
)
as
begin
set nocount on
declare @returncode int
declare @dbid int = db_id()
declare @hasAccess bit

-- Check permissions, must have database control permissions to kill handles.
-- Members of the sysadmin server role and those with SERVER CONTROL have implicit
-- DATABASE CONTROL access.
set @hasAccess =
has_perms_by_name(quotename(db_name()), 'DATABASE', 'CONTROL')
if @hasAccess = 0 OR @hasAccess IS NULL
begin
raiserror(33428,-1,-1,@dbid)
return 1
end

EXEC @returncode = sys.sp_kill_filestream_non_transacted_handles_internal @table_name, @handle_id

return @returncode
end

sp_ivindexhasnullcols (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_ivindexhasnullcols(nvarchar @viewname)

MetaData:

 create procedure sys.sp_ivindexhasnullcols (  
@viewname sysname
,@fhasnullcols bit OUTPUT
)
as
begin
declare @f_ind_unique bit
,@f_ind_clustered bit
,@ivobject_id int
,@indkey int
,@key sysname
,@retcode int

exec @retcode = sys.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)
--
-- validate view object
--
select @ivobject_id = object_id(@viewname)
,@fhasnullcols = 0

if (@ivobject_id IS NULL or @ivobject_id = 0)
begin
raiserror(15390, 16, 1, @viewname)
return 1
end

--
-- get the clustered index and validate
--
select @f_ind_unique = case (status & 2) when 0 then 0 else 1 end
,@f_ind_clustered = case (status & 16) when 0 then 0 else 1 end
from dbo.sysindexes
where id = @ivobject_id and indid = 1

if (@f_ind_unique != 1) or (@f_ind_clustered != 1)
begin
raiserror(21751, 16, 1, @viewname)
return 1
end

--
-- create an enumeration of all the columns that are part of the view index
--
create table #indcoltab(vindexcol int identity, keyname sysname collate database_default not null)
select @indkey = 1
while (@indkey <= 16)
begin
select @key = index_col( @viewname, 1, @indkey )
if (@key is null)
break
else
insert into #indcoltab(keyname) values(@key)

select @indkey = @indkey + 1
end

--
-- We should not have any column participating in this index
-- that allows NULL if we do
-- mark the output flag to TRUE
--
if exists (select *
from sys.columns
where object_id = @ivobject_id
and is_nullable = 1
and name in (select keyname from #indcoltab))
begin
select @fhasnullcols = 1
end

--
-- all done, cleanup and return
--
drop table #indcoltab
return 0
end

sp_is_makegeneration_needed (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_is_makegeneration_needed(int @wait)

MetaData:

   
-- check if we need to call sp_MSmakegeneration now.
-- By default, we don't call MakeGeneration within 0.5 second from the last call
-- In the future, we can change the waiting time by setting @wait parameter
-- also, when we have generation 0 we need to call MakeGeneration in this sync
create procedure sys.sp_is_makegeneration_needed
@wait int = 1, -- default behavior is 500 millisecond, the @wait is in seconds so we use -1 special value for the default value.
@needed int = 1 OUTPUT
as

declare @dt datetime
, @dt2 datetime
, @retcode smallint

-- Security check
exec @retcode = sys.sp_MSrepl_PAL_rolecheck
if (@retcode <> 0) or (@@error <> 0)
return 1

-- If we need to resolve conflicts on this replica we need to call sp_MSmakegeneration
-- Check if any row was put in generation 0 as part of conflict resolution.
-- note that changes on join filtered rows will result on generation 0 in MSmerge_contents table.

if @wait <= 0 or @wait >= 2147483 -- if @wait is -1 (default value) or longer than the biggest value @waitinmilliseconds can hold, reset to 500 milliseconds.
begin
select @wait = 1
end

if exists( select * from dbo.MSmerge_contents with (NOLOCK) where generation = 0)
begin
select @needed = 1
return
end

if exists( select * from dbo.MSmerge_tombstone with (NOLOCK) where generation = 0)
begin
select @needed = 1
return
end

-- obtain the last time MakeGeneration is called from sysmergesubscriptions
-- if the last_makegeneration_datetime is all NULL, it means MakeGeneration has never happened.
select @dt2 = max(last_makegeneration_datetime) from dbo.sysmergesubscriptions
where db_name = db_name()
and UPPER(subscriber_server) collate database_default = UPPER(@@servername) collate database_default

if @dt2 is null
begin
select @needed = 1
return
end

set @dt = getdate()

if datediff(dd, @dt2, @dt) = 0
begin
if @wait > datediff(ss, @dt2, @dt) and 0 < datediff(ss, @dt2, @dt)
begin
select @needed = 0
return
end
end

select @needed = 1
return

sp_invalidate_textptr (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_invalidate_textptr(varbinary @TextPtrValue)

MetaData:

 create procedure sys.sp_invalidate_textptr  
@TextPtrValue varbinary(16) = 0x00
as
dbcc invalidate_textptr(@TextPtrValue)
return (0); -- sp_invalidate_textptr

Total Pageviews