May 21, 2012

sp_MSmatchkey (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_MSmatchkey(nvarchar @tablename
, nvarchar @col1
, nvarchar @col2
, nvarchar @col3
, nvarchar @col4
, nvarchar @col5
, nvarchar @col6
, nvarchar @col7
, nvarchar @col8
, nvarchar @col9
, nvarchar @col10
, nvarchar @col11
, nvarchar @col12
, nvarchar @col13
, nvarchar @col14
, nvarchar @col15
, nvarchar @col16)

MetaData:

   
create proc sys.sp_MSmatchkey
@tablename nvarchar(517),
@col1 nvarchar(258),
@col2 nvarchar(258) = null,
@col3 nvarchar(258) = null,
@col4 nvarchar(258) = null,
@col5 nvarchar(258) = null,
@col6 nvarchar(258) = null,
@col7 nvarchar(258) = null,
@col8 nvarchar(258) = null,
@col9 nvarchar(258) = null,
@col10 nvarchar(258) = null,
@col11 nvarchar(258) = null,
@col12 nvarchar(258) = null,
@col13 nvarchar(258) = null,
@col14 nvarchar(258) = null,
@col15 nvarchar(258) = null,
@col16 nvarchar(258) = null
as

create table #t1 ( -- Join into this... --
i int NOT NULL,
name nvarchar(258) COLLATE database_default NULL
)

create table #i1 (
i int NOT NULL
)

declare @id int, @ii int, @colnotfound nvarchar(258), @keycnt int
select @id = object_id(@tablename)
if (@id is null) begin
RAISERROR (15001, -1, -1, @tablename)
return 1
end
select @ii = 1
insert #t1 values (1, @col1)
insert #t1 values (2, @col2)
insert #t1 values (3, @col3)
insert #t1 values (4, @col4)
insert #t1 values (5, @col5)
insert #t1 values (6, @col6)
insert #t1 values (7, @col7)
insert #t1 values (8, @col8)
insert #t1 values (9, @col9)
insert #t1 values (10, @col10)
insert #t1 values (11, @col11)
insert #t1 values (12, @col12)
insert #t1 values (13, @col13)
insert #t1 values (14, @col14)
insert #t1 values (15, @col15)
insert #t1 values (16, @col16)
delete #t1 where name is null

select @colnotfound = min(name) from #t1 where name not in (select name from dbo.syscolumns where id = @id)
if (@colnotfound is not null) begin
RAISERROR (14305, -1, -1, @colnotfound, @tablename)
return 1
end
select @ii = 1, @keycnt = count(*) from #t1

-- Load all indexes which have the matching number of columns into a temp table, then eliminate those which don't qualify. --
-- Remember the RID in the nc index is counted as a key --
insert #i1 select indid from dbo.sysindexes where status & 0x1800 <> 0
and id = @id and keycnt - (case indid when 1 then 0 else 1 end) = @keycnt
while (@ii <= @keycnt) begin
delete #i1 from #i1 i, #t1 t where t.i = @ii and index_col(@tablename, i.i, t.i) <> t.name
select @ii = @ii + 1
end

-- The qualifying key will be the lowest indid (or the ONLY indid, if we disallow duplicate indexes), if any remain. --
select name from dbo.sysindexes where id = @id and indid = (select min(i) from #i1)

sp_MSmakedynsnapshotvws (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_MSmakedynsnapshotvws(nvarchar @publication
, nvarchar @dynamic_filter_login
, nvarchar @dynamic_snapshot_views_table_name)

MetaData:

   
create procedure sys.sp_MSmakedynsnapshotvws(
@publication sysname,
@dynamic_filter_login sysname,
@dynamic_snapshot_views_table_name sysname = NULL
)
as
begin
set nocount on
declare @retcode int
declare @string_literalized_dynamic_filter_login nvarchar(4000)
declare @articles_cursor_allocated bit,
@articles_cursor_opened bit
declare @pubid uniqueidentifier
declare @partition_id int
declare @publication_number int
declare @dynamic_filter_hostname sysname
declare @get_dynamic_filter_login_fn nvarchar(350)
declare @get_dynamic_filter_login_value nvarchar(300)
declare @stmt nvarchar(4000)

select @articles_cursor_allocated = 0
select @articles_cursor_opened = 0
select @retcode = 0
-- Security check

exec @retcode = sys.sp_MSreplcheck_publish
if @retcode <> 0 or @@error <> 0
return 1

if @dynamic_filter_login is null or @dynamic_filter_login = N''
begin
select @dynamic_filter_login = suser_sname()
end
if @@error<>0
begin
return 1
end
select @string_literalized_dynamic_filter_login =
fn_replmakestringliteral(@dynamic_filter_login) collate database_default

select @dynamic_filter_hostname = host_name()

-- map the dynamic filter longin to a partition id
select @partition_id = -1
exec @retcode = sys.sp_MSget_subscriber_partition_id
@publication,
@partition_id output,
NULL,
@dynamic_filter_hostname,
@dynamic_filter_login
if @@error <> 0 or @retcode <> 0 or @partition_id is NULL or @partition_id = -1
begin
raiserror(20629, 16, -1)
return 1
end

-- Compute the proper view creation order, code is copied from sp_MSpublicationview
declare @progress int
declare @article_level int
select @progress = 1
select @article_level = 0
select @pubid = pubid, @publication_number = publication_number
from dbo.sysmergepublications
where upper(publisher) = upper(publishingservername())
and publisher_db = db_name()
and name = @publication
if @pubid is null
begin
raiserror(20026,11,-1,@publication)
end

select @get_dynamic_filter_login_value = sys.fn_MSget_dynamic_filter_login(@publication_number,@partition_id)
select @get_dynamic_filter_login_value = 'N''' + replace(@get_dynamic_filter_login_value, '''', '''''') + ''''

create table #creation_order
(
creation_order int identity not null,
art_nick int not null,
article_level int not null
)
if @@error<>0
begin
return 1
end

while @progress > 0
begin
insert into #creation_order(art_nick, article_level)
select nickname, @article_level
from dbo.sysmergearticles
where pubid=@pubid
and nickname not in (select art_nick from #creation_order)
and nickname not in
(select art_nickname from dbo.sysmergesubsetfilters
where pubid=@pubid and (filter_type & 1) = 1 and join_nickname not in
(select art_nick from #creation_order))
select @progress = @@rowcount
select @article_level = @article_level + 1
end

-- Create temp table for storing the view definitions and
-- also for acting as a symbol table for views
create table #view_defs_and_syms
(
creation_order int identity,
original_view_name sysname collate database_default,
dynamic_snapshot_view_name sysname collate database_default,
dynamic_snapshot_view_definition nvarchar(max) collate database_default
)
if @@error<>0
begin
return 1
end
-- By the time this procedure is called by the snapshot agent, a regular
-- snapshot is assumed to have been generated.
-- The specified publication is assumed to have dynamic filtering enabled
-- Open a transaction to make sure that no stale temporary views
-- can be lying around for longer than they have to.

begin transaction
save transaction sp_MSmakedynsnapshotartvws

-- Create a global temporary table to track the sync-views
-- that we are about to create here.
if @dynamic_snapshot_views_table_name is NULL
begin
select @dynamic_snapshot_views_table_name = N'##DYN_VIEWS_' +
replace(convert(nvarchar(36), newid()), N'-', N'_')

set @stmt = 'create table ' + quotename(@dynamic_snapshot_views_table_name) + '
(
artid uniqueidentifier primary key,
dynamic_snapshot_view_name sysname not null unique
)'

exec(@stmt)
if @@error<>0
begin
goto Failure
end
end

declare hArticles cursor local fast_forward for
select artid, sync_objid, co.art_nick
from dbo.sysmergearticles sma
inner join #creation_order co
on sma.nickname = co.art_nick
where sma.pubid = @pubid
order by co.creation_order asc
if @@error<>0
begin
goto Failure
end
select @articles_cursor_allocated = 1
open hArticles
if @@error<>0
begin
goto Failure
end
select @articles_cursor_opened = 1

declare @sync_objid int
declare @artid uniqueidentifier
declare @dynamic_snapshot_view_name sysname
declare @original_view_name sysname
declare @insert_command nvarchar(4000)
declare @min_id int
declare @db_name sysname
declare @original_view_definition nvarchar(max)
declare @artnick int

select @db_name = db_name()

-- Stage 1: Build up the symbol table with
-- regular sync view definitions
fetch hArticles into @artid, @sync_objid, @artnick

while (@@fetch_status<>-1)
begin
declare @row_number int, @max_row_number int

select @max_row_number = 1, @row_number = 1

-- Get name of the original sync view
select @original_view_name = object_name(@sync_objid)

-- Construct name for the dynamic snapshot view
select @dynamic_snapshot_view_name = N'MSmerge_DYN_VIEW_' +
replace(convert(nvarchar(20), @artnick), N'-', N'_') + '_' +
replace(convert(nvarchar(36), newid()), N'-', N'_')

select @max_row_number = max(colid)
from dbo.syscomments
where id = @sync_objid

select @row_number = 1, @original_view_definition = N''
while @row_number <= @max_row_number
begin
select @original_view_definition = @original_view_definition + text
from dbo.syscomments
where id = @sync_objid
and colid = @row_number

select @row_number = @row_number + 1
end

-- Insert a row into the symbol and view def table
-- for further processing

-- Neither original_view_name nor dynamic_snapshot_view_name should be quoted here as both columns
-- are sysnames and could potentially be truncated
insert #view_defs_and_syms
values (@original_view_name,
@dynamic_snapshot_view_name,
@original_view_definition)
if @@error<>0
begin
goto Failure
end

select @insert_command = N'insert ' + quotename(@dynamic_snapshot_views_table_name) + ' values (@artid, @dynamic_snapshot_view_name)'
-- Insert the mapping into the global temp table
exec @retcode = sys.sp_executesql
@insert_command,
N'@artid uniqueidentifier, @dynamic_snapshot_view_name sysname',
@artid = @artid,
@dynamic_snapshot_view_name = @dynamic_snapshot_view_name

if @@error<>0 or @retcode<>0
begin
goto Failure
end

-- Insert the view name into the global tracking table so it is
-- guaranteed to be cleaned up properly
insert MSdynamicsnapshotviews values (@dynamic_snapshot_view_name)
if @@error<>0
begin
goto Failure
end

fetch hArticles into @artid, @sync_objid, @artnick
end
close hArticles
select @articles_cursor_opened = 0
deallocate hArticles
select @articles_cursor_allocated = 0
-- Stage 2: Fix-up all the internal references to non-dynamic snapshot
-- views inside the dynamic snapshot view definitions and
-- create the views along the way.

-- Cursor state variables for ensuring proper cursor cleanup
declare @viewdefs_cursor_allocated bit,
@viewdefs_cursor_opened bit,
@symbols_cursor_allocated bit,
@symbols_cursor_opened bit

declare @dynamic_snapshot_view_definition nvarchar(max)
declare @dynamic_filter_login_fn_definition nvarchar(max)
declare @dynamic_snapshot_view_name_symbol sysname
declare @qualified_dynamic_snapshot_view_name nvarchar(4000)

select @viewdefs_cursor_allocated = 0,
@viewdefs_cursor_opened = 0,
@symbols_cursor_allocated = 0,
@symbols_cursor_opened = 0

-- Declare a cursor for each dynamic snapshot view definition
declare hViewDefs cursor local fast_forward for
select dynamic_snapshot_view_name,
dynamic_snapshot_view_definition
from #view_defs_and_syms
order by creation_order asc

if @@error<>0
begin
goto Failure
end
select @viewdefs_cursor_allocated = 1

-- Declare a static cursor for symbol lookup so we can go back to the
-- first row without tearing down the cursor
declare hSymbols cursor local static for
select original_view_name,
dynamic_snapshot_view_name
from #view_defs_and_syms
if @@error<>0
begin
goto Failure
end
select @symbols_cursor_allocated = 1

open hViewDefs
if @@error<>0
begin
goto Failure
end
select @viewdefs_cursor_opened = 1
open hSymbols
if @@error<>0
begin
goto Failure
end
select @symbols_cursor_opened = 1

-- For each view definition...
fetch hViewDefs into @dynamic_snapshot_view_name,
@dynamic_snapshot_view_definition
while (@@fetch_status<>-1)
begin

select @get_dynamic_filter_login_fn = N'dbo.' + quotename(@dynamic_snapshot_view_name + N'_FN')

-- For each dynamic snapshot view name to original view name mapping...
fetch first from hSymbols into @original_view_name,
@dynamic_snapshot_view_name_symbol
while (@@fetch_status<>-1)
begin
-- Do view name replacement
select @qualified_dynamic_snapshot_view_name = quotename(@dynamic_snapshot_view_name_symbol)

select @dynamic_snapshot_view_definition =
replace(@dynamic_snapshot_view_definition,
quotename(@original_view_name),
@qualified_dynamic_snapshot_view_name)
fetch hSymbols into @original_view_name,
@dynamic_snapshot_view_name_symbol
end
-- Replace suser_sname() with string literalized version of the given
-- dynamic filter login
select @dynamic_snapshot_view_definition =
replace(@dynamic_snapshot_view_definition collate SQL_Latin1_General_CP1_CI_AS,
N'suser_sname()' collate SQL_Latin1_General_CP1_CI_AS,
@get_dynamic_filter_login_fn + N'()' collate SQL_Latin1_General_CP1_CI_AS)

-- Replace system_user with string literalized version of the given
-- dynamic filter login
select @dynamic_snapshot_view_definition =
replace(@dynamic_snapshot_view_definition collate SQL_Latin1_General_CP1_CI_AS,
N'system_user' collate SQL_Latin1_General_CP1_CI_AS,
@get_dynamic_filter_login_fn + N'()' collate SQL_Latin1_General_CP1_CI_AS)
if datalength(@dynamic_snapshot_view_definition) = 7000
begin
raiserror(21387,16,-1)
goto Failure
end


-- Create the function with schema binding and mark it as a system object.
-- @get_dynamic_filter_login_fn is already quoted, so there is no need to quote it here
select @dynamic_filter_login_fn_definition = N'
create function '
+ @get_dynamic_filter_login_fn + N'()
returns sysname
WITH SCHEMABINDING
as
begin
return '
+ @get_dynamic_filter_login_value + N'
end'


exec (@dynamic_filter_login_fn_definition)
if @@error<>0
begin
goto Failure
end
exec @retcode = sys.sp_MS_marksystemobject @get_dynamic_filter_login_fn
if @@error<>0 or @retcode<>0
begin
goto Failure
end

-- Create the view and mark it as a system object
exec (@dynamic_snapshot_view_definition)
if @@error<>0
begin
goto Failure
end
exec @retcode = sys.sp_MS_marksystemobject @dynamic_snapshot_view_name
if @@error<>0 or @retcode<>0
begin
goto Failure
end
fetch hViewDefs into @dynamic_snapshot_view_name,
@dynamic_snapshot_view_definition
end

close hViewDefs
deallocate hViewDefs
close hSymbols
deallocate hSymbols
drop table #view_defs_and_syms
drop table #creation_order
commit transaction

-- Construct the result set
declare @dynamic_snapshot_view_owner sysname

-- Since all views will have the same owner, we may as well just
-- find it once and then return it in the second row of the result set
-- (see header)

-- Note: Even the cursors are closed, the @dynamic_snapshot_view_name
-- variable should still contain a valid value

select @dynamic_snapshot_view_owner = SCHEMA_NAME(schema_id)
from sys.objects
where name = @dynamic_snapshot_view_name
declare @result_command nvarchar(4000)
select @result_command = '
select '
'article_name'' = null,
'
'dynamic_snapshot_view_name'' = ' +
fn_replmakestringliteral(@dynamic_snapshot_views_table_name) collate database_default + N'
union all
select null, '
+
coalesce(fn_replmakestringliteral(@dynamic_snapshot_view_owner) collate database_default, N'null') collate database_default + N'
union all
select sma.name, dsvt.dynamic_snapshot_view_name
from dbo.sysmergearticles sma
inner join '
+ quotename(@dynamic_snapshot_views_table_name) + N' dsvt
on sma.artid = dsvt.artid
where sma.pubid = '
'' + convert(nvarchar(128), @pubid) + ''''

exec (@result_command)
if @@error<>0
return 1

return 0
Failure:
if @articles_cursor_opened = 1
begin
close hArticles
end
if @articles_cursor_allocated = 1
begin
deallocate hArticles
end
if @viewdefs_cursor_opened = 1
begin
close hViewDefs
end
if @viewdefs_cursor_allocated = 1
begin
deallocate hViewDefs
end
if @symbols_cursor_opened = 1
begin
close hSymbols
end
if @symbols_cursor_allocated = 1
begin
deallocate hSymbols
end
drop table #view_defs_and_syms
drop table #creation_order
rollback transaction sp_MSmakedynsnapshotartvws
commit transaction
return 1
end

sp_MSmerge_ddldispatcher (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_MSmerge_ddldispatcher(xml @EventData
, int @procmapid)

MetaData:

 create procedure sys.sp_MSmerge_ddldispatcher   
(
@EventData xml
,@procmapid int
)
AS
begin
set nocount on

if is_member('db_owner') <> 1
begin
raiserror (21050, 16, -1)
return (1)
end

-- validate the procmapid
if @procmapid not in (1,2,3,4)
begin
raiserror(15021, 16, -1, '@procmapid')
goto FAILURE
end

declare @debug_print bit
if object_id('MSrepl_debug_DDL') is not null
set @debug_print = 1
else
set @debug_print = 0


declare @object_name sysname
,@object_owner sysname
,@qual_object_name nvarchar(512) -- qualified 3-part-name
,@objid int
,@objecttype varchar(32)
,@encrypted nvarchar(32)
,@pass_through_scripts nvarchar(max)
,@eventDoc int
,@db_name sysname
,@targetobject nvarchar(51)



set @targetobject=N''
-- parse event data
select @object_name = event_instance.value('ObjectName[1]', 'sysname')
,@object_owner = event_instance.value('SchemaName[1]', 'sysname')
,@objecttype = event_instance.value('ObjectType[1]', 'varchar(32)')
,@encrypted = event_instance.value('(TSQLCommand/SetOptions/@ENCRYPTED)[1]', 'nvarchar(32)')
,@pass_through_scripts = event_instance.value('(TSQLCommand/CommandText)[1]', 'nvarchar(max)')
,@targetobject = event_instance.value('TargetObjectName[1]', 'nvarchar(512)')
FROM @EventData.nodes('/EVENT_INSTANCE') as R(event_instance)


if @debug_print = 1
select 'stage' = 'xmlnoderefs : '
, '@object_name' = @object_name
, '@object_owner' = @object_owner
, '@objecttype' = @objecttype
, '@encrypted' = @encrypted
, '@pass_through_scripts' = @pass_through_scripts
, '@targetobject' = @targetobject

-- If the object being manipulated is a database level trigger that is owned by replication, raise error
if exists (select * from sys.triggers where name = @object_name and parent_class = 0 and @objecttype = 'TRIGGER' and @object_name in (N'MSmerge_tr_altertable', N'MSmerge_tr_alterview', N'MSmerge_tr_alterschemaonly', N'MSmerge_tr_altertrigger'))
begin
raiserror(21598, 16, 1)
goto FAILURE
end

-- If the object being manipulated is a database level trigger that is not owned by replication, return immediately
if exists (select * from sys.triggers where name = @object_name and parent_class = 0 and @objecttype = 'TRIGGER' and @object_name not in (N'MSmerge_tr_altertable', N'MSmerge_tr_alterview', N'MSmerge_tr_alterschemaonly', N'MSmerge_tr_altertrigger'))
return 0


select @qual_object_name = QUOTENAME(@object_owner) + N'.' + QUOTENAME(@object_name)
select @objid = object_id(@qual_object_name)
select @db_name=db_name()

if @debug_print = 1
select 'stage' = 'quotename : '
, '@qual_object_name' = @qual_object_name
, '@objid' = @objid
, '@dbname' = @db_name

if @objid is NULL
begin
-- add error message
goto FAILURE
end

-- can not alter to 'with encrypted' if object is published
-- return immediately if object is not published
if UPPER(@encrypted) = N'TRUE'
begin
if object_id('dbo.sysmergeextendedarticlesview') is not null
begin
if (UPPER(@objecttype) != 'TRIGGER' and
exists (SELECT * FROM dbo.sysmergeextendedarticlesview WHERE objid = @objid)
)
begin
raiserror(21815, 16, 1, @qual_object_name)
goto FAILURE
end
end
else if (UPPER(@objecttype) = 'TRIGGER' and object_id('dbo.sysmergearticles') is not null)
begin
if exists (select * from sysmergearticles a join sys.objects o on a.objid = o.parent_object_id where o.object_id = @objid)
begin
raiserror(21815, 16, 1, @qual_object_name)
goto FAILURE
end
end
else
return 0
end

--
-- remove 3 part naming
exec sys.sp_replgetparsedddlcmd
@pass_through_scripts output
, N'ALTER'
,@objecttype
,@db_name
,@object_owner
,@object_name
,@targetobject
--
select @pass_through_scripts = sys.fn_replgetparsedddlcmd(@pass_through_scripts
,N'ALTER'
,@objecttype
,@db_name
,@object_owner
,@object_name
,@targetobject)

-- sys.fn_replgetparsedddlcmd will return empty string if DDL contains
-- syntax that we don't currently handle (after Katmai DDL
-- improvement)
if @pass_through_scripts = N''
return 0

-- if merge replication is not enabled for this db, or object is not published, don''t do anything
-- refer to sp_MScreate_mergesystables for sys tables
if object_id('dbo.sysmergearticles') is not null
begin
declare @proc_name sysname
,@retcode int

-- Security Check
EXEC @retcode = sys.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
goto FAILURE

select @proc_name = case
when (@procmapid = 1) then 'sys.sp_MSmerge_altertable'
when (@procmapid = 2) then 'sys.sp_MSmerge_alterview'
when (@procmapid = 3) then 'sys.sp_MSmerge_alterschemaonly'
when (@procmapid = 4) then 'sys.sp_MSmerge_altertrigger'
end
if (@objecttype != 'TRIGGER' and exists (SELECT * FROM dbo.sysmergeextendedarticlesview WHERE objid = @objid))
begin
if (@procmapid = 1 or @procmapid = 4 )
begin
exec @retcode = @proc_name @qual_object_name, @objid, @pass_through_scripts
if (@@error <> 0 or @retcode <> 0)
goto FAILURE
end
else if (@procmapid = 2 or @procmapid = 3)
begin
exec @retcode = @proc_name @qual_object_name, @objid, @pass_through_scripts, @objecttype
if (@@error <> 0 or @retcode <> 0)
goto FAILURE
end
end
if (@objecttype = 'TRIGGER' and object_id('dbo.sysmergearticles') is not null)
begin
if exists (select * from sysmergearticles a join sys.objects o on a.objid = o.parent_object_id where o.object_id = @objid)
begin
exec @retcode = @proc_name @qual_object_name, @objid, @pass_through_scripts, @targetobject
if (@@error <> 0 or @retcode <> 0)
goto FAILURE
end
end
end
return 0

FAILURE:
-- this rolls back the implicit transaction
-- all changes within this transaction is rolled back.
ROLLBACK TRANSACTION
raiserror(21530, 16, -1)
return (1)
end

sp_MSmerge_alterview (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_MSmerge_alterview(nvarchar @qual_object_name
, int @objid
, nvarchar @pass_through_scripts
, varchar @objecttype)

MetaData:

   




create procedure sys.sp_MSmerge_alterview
(
@qual_object_name nvarchar(512) -- qualified 3-part-name
,@objid int
,@pass_through_scripts nvarchar(max)
,@objecttype varchar(32)
)
AS
set nocount on
declare @retcode int
declare @got_merge_admin_applock bit

select @got_merge_admin_applock = 0

if is_member('db_owner') <> 1
begin
raiserror (21050, 16, -1)
return (1)
end

begin tran
save TRAN sp_MSmerge_alterview

-- obtain the snapshot/DDL/admin proc applock to avoid having contention with snapshot
-- Attempt to get the lock with no wait
exec @retcode = sys.sp_MSgetmergeadminapplock @timeout = 0, -- no wait
@lockowner = N'Transaction'
if @retcode<>0 or @@error<>0
begin
raiserror(21386, 16, -1, @qual_object_name)
goto FAILURE
end

select @got_merge_admin_applock = 1

-- loop through schema only publications/articles
exec @retcode = sys.sp_MSmerge_alterschemaonly @qual_object_name = @qual_object_name
,@objid = @objid
,@pass_through_scripts = @pass_through_scripts
,@objecttype = @objecttype
if @retcode <>0 or @@ERROR<>0
goto FAILURE

-- merge does not have table based publications/articles for now
exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
commit tran
return 0

FAILURE:
IF @@TRANCOUNT > 0
begin
if @got_merge_admin_applock=1
exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
ROLLBACK TRANSACTION sp_MSmerge_alterview
commit tran
end
return 1

sp_MSmerge_altertrigger (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_MSmerge_altertrigger(nvarchar @qual_object_name
, int @objid
, nvarchar @pass_through_scripts
, nvarchar @target_object_name)

MetaData:

 create procedure sys.sp_MSmerge_altertrigger   
(
@qual_object_name nvarchar(512) -- qualified 3-part-name
,@objid int
,@pass_through_scripts nvarchar(max)
,@target_object_name nvarchar(512)
)
AS
set nocount on
declare @merge_artid uniqueidentifier
,@pubid uniqueidentifier
,@retcode int
,@subscription_active int
,@include_ddl int
,@replicate_trigger int
,@dest_table sysname
,@dest_owner sysname
,@qual_dest_object nvarchar(512)
,@temp_ddlcmds nvarchar(max)

select @include_ddl = 0x1 -- replicate_ddl is turned on by sp_addmergepublication
,@subscription_active = 1 -- 1 for active
,@retcode = 0
,@replicate_trigger = 0x100

declare @publisher sysname,
@publisher_db sysname,
@is_publisher bit,
@islightweight bit,
@qual_dest_object2 nvarchar(512)

declare @got_merge_admin_applock bit

select @got_merge_admin_applock = 0

if is_member('db_owner') <> 1
begin
raiserror (21050, 16, -1)
return (1)
end

-- Check if hws or lws, return if not heavyweight.
select top 1 @islightweight= lightweight
from dbo.sysmergearticles where object_name(objid)=@target_object_name

if @islightweight is null
begin
return 0
end

-- alter trigger only can be performed only from original publisher of the table.
-- except from merge agent, which propagate ALTER statement to subscribers
if sessionproperty('replication_agent') <> 1
begin
if 0=@islightweight
begin
if NOT exists (select * from dbo.sysmergearticles a join sys.objects o
on
a.objid = o.parent_object_id
where
o.object_id = @objid and
a.pubid in
(select pubid from dbo.sysmergepublications
where upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS) and
publisher_db=db_name()))
begin
raiserror(21531, 16, -1)
return 1
end
end
else
begin
raiserror(21531, 16, -1)
return 1
end
end

-- real work
declare @snapshot_ready int, @sync_mode int, @replicate_ddl int

begin tran
save tran sp_MSmerge_altertrigger

-- obtain the snapshot/DDL/admin proc applock to avoid having contention with snapshot
-- Attempt to get the lock with no wait
exec @retcode = sys.sp_MSgetmergeadminapplock @timeout = 0, -- no wait
@lockowner = N'Transaction'
if @retcode<>0 or @@error<>0
begin
raiserror(21386, 16, -1, @qual_object_name)
goto FAILURE
end

select @got_merge_admin_applock = 1

-- loop through relavent publications/articles: we must be in heavy weight as we will use sysmergearticles
declare #mergepubarticle CURSOR LOCAL FAST_FORWARD for
select DISTINCT a.artid, a.pubid, p.publisher, p.publisher_db, p.snapshot_ready, p.sync_mode, p.replicate_ddl
,a.destination_object, a.destination_owner
from sysmergearticles a
join sys.objects o on a.objid = o.parent_object_id
join sysmergepublications p on a.pubid = p.pubid
join dbo.sysmergesubscriptions s on a.pubid = s.pubid
where o.object_id = @objid
and s.status = @subscription_active
and (p.replicate_ddl & @include_ddl) = @include_ddl
and (a.schema_option & @replicate_trigger) = @replicate_trigger

open #mergepubarticle
fetch #mergepubarticle into @merge_artid, @pubid, @publisher, @publisher_db, @snapshot_ready, @sync_mode, @replicate_ddl
,@dest_table, @dest_owner
while (@@fetch_status <> -1)
BEGIN
-- check if this publication is at publisher
if (
upper(@publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS)
and db_name()=@publisher_db
)
begin
set @is_publisher= 1
end
else
begin
set @is_publisher= 0
end

if (1=@is_publisher and 1=@snapshot_ready)
begin
-- translation for SSCE
if @sync_mode=1 and @replicate_ddl&1<>0
begin
set @sync_mode=1
exec @retcode = sys.sp_MSNonSQLDDLForSchemaDDL @artid = @merge_artid
, @pubid=@pubid
,@ddlcmd = @pass_through_scripts
end
-- pass through ddl text for normal db
if @replicate_ddl&1<>0
begin
-- use 2 part naming
if(@dest_owner is not NULL) and (len(@dest_owner) > 0)
select @qual_dest_object2 = QUOTENAME(@dest_owner) + N'.'
else
select @qual_dest_object2 = N''
if(@dest_table is not null) and (len(@dest_table) > 0)
select @qual_dest_object2 = @qual_dest_object2 + QUOTENAME(@dest_table)
else
select @qual_dest_object2 = @target_object_name
-- prepare ddl cmd
-- select @temp_ddlcmds = N'ALTER TRIGGER '
-- + @qual_object_name + N' on '
-- + @qual_dest_object2 + N' '
-- + @pass_through_scripts


select @temp_ddlcmds = N'if object_id(N''' + sys.fn_replreplacesinglequote(@qual_object_name) + N''') is not null exec('''
+ sys.fn_replreplacesinglequote('ALTER TRIGGER '
+ @qual_object_name + N' on '
+ @qual_dest_object2 + N' '
+ @pass_through_scripts )+ N''')'

-- save it
exec @retcode = sys.sp_MSmerge_passDDLcmd @artid=@merge_artid
, @pubid=@pubid
,@ddlcmd = @temp_ddlcmds
end
if @retcode <>0 or @@ERROR<>0
goto DROPTRAN
end
fetch #mergepubarticle into @merge_artid, @pubid, @publisher, @publisher_db, @snapshot_ready, @sync_mode, @replicate_ddl
,@dest_table, @dest_owner

END
close #mergepubarticle
deallocate #mergepubarticle

exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
commit tran
return 0

DROPTRAN:
close #mergepubarticle
deallocate #mergepubarticle

FAILURE:
IF @@TRANCOUNT > 0
begin
if @got_merge_admin_applock=1
exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
rollback tran sp_MSmerge_altertrigger
commit tran
end
return 1

sp_MSmerge_alterschemaonly (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_MSmerge_alterschemaonly(nvarchar @qual_object_name
, int @objid
, nvarchar @pass_through_scripts
, varchar @objecttype)

MetaData:

 create procedure sys.sp_MSmerge_alterschemaonly   
(
@qual_object_name nvarchar(512) -- qualified 3-part-name
,@objid int
,@pass_through_scripts nvarchar(max)
,@objecttype varchar(32)
)
AS
set nocount on
declare @merge_artid uniqueidentifier
,@pubid uniqueidentifier
,@retcode int
,@subscription_active int
,@include_ddl int

declare @publisher sysname,
@publisher_db sysname,
@is_publisher bit,
@islightweight bit

declare @dest_table sysname
,@dest_owner sysname
,@qual_dest_object nvarchar(512)

declare @got_merge_admin_applock bit

select @got_merge_admin_applock = 0

select @include_ddl = 0x1 -- replicate_ddl is turned on by sp_addmergepublication
,@subscription_active = 1 -- 1 for active
,@retcode = 0

if is_member('db_owner') <> 1
begin
raiserror (21050, 16, -1)
return (1)
end

-- alter schema only can be performed only from original publisher of the table.
-- except from merge agent, which propagate ALTER statement to subscribers
if sessionproperty('replication_agent') <> 1
begin
if NOT exists (select * from dbo.sysmergeextendedarticlesview
where objid=@objid and
pubid in
(select pubid from dbo.sysmergepublications
where upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS) and
publisher_db=db_name()))
begin
raiserror(21531, 16, -1)
return 1
end
end

-- add object owner, object name to pass through script

-- this will always get the pubid of the publisher
-- if this cmd was executed at the subscriber, error would be thrown by the prev 'IF' condition
select @pubid = pubid from dbo.sysmergepublications
where upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS) and
publisher_db=db_name()

select @dest_table=destination_object, @dest_owner=destination_owner
from dbo.sysmergeextendedarticlesview
where pubid=@pubid and objid=@objid

if(@dest_owner is not NULL) and (len(@dest_owner) > 0)
select @qual_dest_object = QUOTENAME(@dest_owner) + N'.'
else
select @qual_dest_object = N''

if(@dest_table is not null) and (len(@dest_table) > 0)
select @qual_dest_object = @qual_dest_object + QUOTENAME(@dest_table)
else
select @qual_dest_object = @qual_object_name

select @pass_through_scripts = N'ALTER ' + @objecttype + N' '
+ @qual_dest_object + N' '
+ @pass_through_scripts


-- real work
declare @snapshot_ready int, @sync_mode int, @replicate_ddl int

begin tran
save tran sp_MSmerge_alterschemaonly

-- obtain the snapshot/DDL/admin proc applock to avoid having contention with snapshot
-- Attempt to get the lock with no wait
exec @retcode = sys.sp_MSgetmergeadminapplock @timeout = 0, -- no wait
@lockowner = N'Transaction'
if @retcode<>0 or @@error<>0
begin
raiserror(21386, 16, -1, @qual_object_name)
goto FAILURE
end

select @got_merge_admin_applock = 1

-- loop through relavent publications/articles: note sysmergeschemaarticles exists in both heavy and light weight
declare #mergepubarticle CURSOR LOCAL FAST_FORWARD for
select DISTINCT a.artid, a.pubid, p.publisher, p.publisher_db, p.snapshot_ready, p.sync_mode, p.replicate_ddl
from sysmergeschemaarticles a
join sysmergepublications p on a.pubid = p.pubid
join dbo.sysmergesubscriptions s on a.pubid = s.pubid
where a.objid = @objid
and s.status = @subscription_active
and (p.replicate_ddl & @include_ddl) = @include_ddl

open #mergepubarticle
fetch #mergepubarticle into @merge_artid, @pubid, @publisher, @publisher_db, @snapshot_ready, @sync_mode, @replicate_ddl
while (@@fetch_status <> -1)
BEGIN
set @islightweight= null
select top 1 @islightweight= lightweight from dbo.sysmergearticles where pubid=@pubid

if 1=@islightweight
begin
set @snapshot_ready=0
set @sync_mode=0
set @replicate_ddl=0
end

-- check if this publication is at publisher
if (
upper(@publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS)
and db_name()=@publisher_db
)
begin
set @is_publisher= 1
end
else
begin
set @is_publisher= 0
end

if (1=@is_publisher and 1=@snapshot_ready)
begin
-- translation for SSCE
if @sync_mode=1 and @replicate_ddl&1<>0
begin
set @sync_mode=1
exec @retcode = sys.sp_MSNonSQLDDLForSchemaDDL @artid = @merge_artid
, @pubid=@pubid
,@ddlcmd = @pass_through_scripts
end
-- pass through ddl text for normal db
if @replicate_ddl&1<>0
begin
exec @retcode = sys.sp_MSmerge_passDDLcmd @artid=@merge_artid
, @pubid=@pubid
,@ddlcmd = @pass_through_scripts
end
if @retcode <>0 or @@ERROR<>0
goto DROPTRAN
end
fetch #mergepubarticle into @merge_artid, @pubid, @publisher, @publisher_db, @snapshot_ready, @sync_mode, @replicate_ddl
END
close #mergepubarticle
deallocate #mergepubarticle

exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
commit tran
return 0

DROPTRAN:
close #mergepubarticle
deallocate #mergepubarticle

FAILURE:
IF @@TRANCOUNT > 0
begin
if @got_merge_admin_applock=1
exec sys.sp_MSreleasemergeadminapplock @lockowner = N'Transaction'
ROLLBACK TRANSACTION sp_MSmerge_alterschemaonly
commit tran
end
return 1

Total Pageviews