April 16, 2012

sp_addtabletocontents (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_addtabletocontents(nvarchar @table_name
, nvarchar @owner_name
, nvarchar @filter_clause)

MetaData:

   
--
-- DEVNOTE: Uses @filterclause. Do not sign.
--
create procedure sys.sp_addtabletocontents
(@table_name sysname,
@owner_name sysname = NULL,
@filter_clause nvarchar(4000) = NULL)
AS
begin
declare @qualified_table_name nvarchar(540)
declare @tablenick int
declare @tablenickstr nvarchar(12)
declare @replnick binary(6)
declare @lineage varbinary(311)
declare @colv varbinary(2953)
declare @coltrack int
declare @objid int
declare @retcode int
declare @gen bigint
declare @marker uniqueidentifier
declare @command nvarchar(max)
declare @artid uniqueidentifier

set nocount on

-- Security Checking
-- sysadmin or db_owner have access

if is_srvrolemember('sysadmin') <> 1 and
is_member('db_owner') <> 1
begin
raiserror(14260, 16, -1)
return (1)
end

execute @retcode = sys.sp_MSgetreplnick @replnick = @replnick output
if (@@error <> 0) or @retcode <> 0 or @replnick IS NULL
begin
RAISERROR (14055, 11, -1)
RETURN(1)
end

if @owner_name is NULL
begin
select @owner_name = SCHEMA_NAME(schema_id) from sys.objects where name = @table_name
end
set @qualified_table_name = QUOTENAME(@owner_name) + '.' + QUOTENAME(@table_name)

set @objid = object_id(@qualified_table_name)
if @objid is NULL return (1)
select top 1 @tablenick = nickname, @coltrack = column_tracking, @artid = artid
from dbo.sysmergearticles where objid = @objid
order by column_tracking desc

if @coltrack = 1
set @colv = 0xFF
else
set @colv = NULL
set @lineage = { fn UPDATELINEAGE(0x0, @replnick, 1) }

set @tablenickstr = convert(nchar, @tablenick)
select @marker = newid()

if @filter_clause is null
select @filter_clause = N''

begin tran
save tran addtabletocontents

if @filter_clause is null or @filter_clause = N''
begin
select @command = 'insert into dbo.MSmerge_contents (tablenick, rowguid, generation, partchangegen, lineage, colv1, marker)
select @tablenick, rowguidcol, @gen, (-@gen), @lineage, @colv, @marker
from '
+ @qualified_table_name + '
where rowguidcol not in (select rowguid from dbo.MSmerge_contents where tablenick = @tablenick)'

end
else
begin
select @command = 'insert into dbo.MSmerge_contents (tablenick, rowguid, generation, partchangegen, lineage, colv1, marker)
select @tablenick, rowguidcol, @gen, (-@gen), @lineage, @colv, @marker
from '
+ @qualified_table_name + '
where ('
+ @filter_clause + ')
and rowguidcol not in (select rowguid from dbo.MSmerge_contents where tablenick = @tablenick)'

end

exec @retcode = sys.sp_MSmerge_getgencur @tablenick = @tablenick, @changecount = 0, @gen_cur = @gen output
if @@error <> 0 or @retcode <> 0
goto Error

exec @retcode = sys.sp_executesql @command,
N'@tablenick int, @gen bigint, @lineage varbinary(311), @colv varbinary(2953), @marker uniqueidentifier',
@tablenick = @tablenick, @gen = @gen, @lineage = @lineage, @colv = @colv, @marker = @marker
if @@error <> 0 or @retcode <> 0
goto Error

exec @retcode = sys.sp_MSevaluate_change_membership_for_row @tablenick = @tablenick, @marker = @marker
if @@error <> 0 or @retcode <> 0
goto Error

exec @retcode = sys.sp_MSevaluate_logicalrecordparent_allcontentsrows @artid = @artid
if @@error <> 0 or @retcode <> 0
goto Error

commit tran

return 0

Error:

rollback tran addtabletocontents
commit tran

return 1
end

No comments:

Post a Comment

Total Pageviews