May 11, 2012

sp_MSenumdeletesmetadata (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_MSenumdeletesmetadata(uniqueidentifier @pubid
, int @maxrows
, varchar @genlist
, int @tablenick
, uniqueidentifier @rowguid
, int @filter_partialdeletes
, int @specified_article_only
, bigint @mingen
, bigint @maxgen
, int @compatlevel
, bit @enumentirerowmetadata)

MetaData:

   
create procedure sys.sp_MSenumdeletesmetadata(
@pubid uniqueidentifier,
@maxrows int,
@genlist varchar(8000),
@tablenick int,
@rowguid uniqueidentifier,
@filter_partialdeletes int = 0,
@specified_article_only int = 0,
@mingen bigint = 0,
@maxgen bigint = 0,
@compatlevel int = 10, -- backward compatibility level, default=Sphinx
@enumentirerowmetadata bit= 1)

as
declare @tnstring nvarchar(12)
declare @pubidstr nvarchar(38)
declare @tablenick_qual nvarchar(100)
declare @generation_clause1 nvarchar(max)
declare @generation_clause2 nvarchar(max)
declare @generation_clause3 nvarchar(max)
declare @last_art_processing_order int
declare @last_art_processing_order_str varchar(12)
declare @rowguid_clause nvarchar(100)

if (@genlist is null)
begin
RAISERROR(14043, 16, -1, '@genlist', 'sp_MSenumdeletesmetadata')
return (1)
end
if object_id('MSmerge_contents') is NULL
begin
RAISERROR(20054 , 16, -1)
return (1)
end

-- security check
if ({ fn ISPALUSER(@pubid) } <> 1)
begin
if (@pubid is NULL)
begin
RAISERROR (21723, 16, -1, 'sp_MSenumdeletesmetadata')
return 1
end
else
begin
RAISERROR (14126, 11, -1)
return 1
end
end

select @generation_clause1 = ' '
select @generation_clause2 = ' '
select @generation_clause3 = ' '



set @pubidstr = '''' + convert(nchar(36), @pubid) + ''''

-- If the filter_partialdeletes is NOT set, include all types of tombstones, else filter the type = 5 ones --
if (@filter_partialdeletes = 0)
set @tablenick_qual = ' and ts.tablenick = sm.nickname '
else
set @tablenick_qual = ' and ts.tablenick = sm.nickname and ts.type <> 5'

declare @selecttop nvarchar(50)
if (@maxrows = 0)
set @selecttop= 'select'
else
set @selecttop= 'select top ' + cast(@maxrows as nvarchar(9))

declare @mingenstr nvarchar(21)
declare @maxgenstr nvarchar(21)

select @mingenstr = convert(nvarchar, @mingen)
select @maxgenstr = convert(nvarchar, @maxgen)

declare @metadatacol nvarchar(200)

if 0 = @enumentirerowmetadata
begin
if 0 = @tablenick or 0 = @specified_article_only
set @metadatacol= 'sys.fn_MSgeneration_downloadonly(generation, tablenick), sys.fn_MSvector_downloadonly(lineage, tablenick)'
else
set @metadatacol= 'null, null'
end
else if @compatlevel >= 90
set @metadatacol= 'generation, lineage'
else
set @metadatacol= 'generation, {fn LINEAGE_90_TO_80(lineage)}'

if (@maxgen = 0)
begin
select @generation_clause1 = ' generation in ('
select @generation_clause2 = rtrim(ltrim(@genlist))
select @generation_clause3 = ') '
end
else if @mingen = @maxgen
select @generation_clause1 = ' generation = ' + @mingenstr + ' '
else
begin
select @generation_clause1 = ' generation >= ' + @mingenstr + ' and generation <= ' + @maxgenstr + ' and
generation in ('

select @generation_clause2 = rtrim(ltrim(@genlist))
select @generation_clause3 = ') '
end

if (@tablenick = 0)
begin
if (@genlist is not null and rtrim(ltrim(@genlist)) <> '')
begin
execute (@selecttop + ' tablenick, rowguid, ' + @metadatacol + ', ts.type
from dbo.MSmerge_tombstone ts, dbo.sysmergearticles sm
where '
+ @generation_clause1 + @generation_clause2 + @generation_clause3 + '
and sm.pubid = '
+ @pubidstr + @tablenick_qual + '
and (ts.tablenick in
(select nickname from dbo.sysmergepartitioninfoview where pubid = '
+ @pubidstr + '
and logical_record_view is null)
or
ts.logical_record_parent_rowguid is null
)
order by processing_order desc, tablenick desc, rowguid asc'
)

IF @@ERROR <>0
begin
return (1)
end


end
end
else
begin
set @tnstring = convert(nchar, @tablenick)
if @rowguid is NULL or @rowguid = '00000000-0000-0000-0000-000000000000'
select @rowguid_clause = ' '
else
select @rowguid_clause = ' and rowguid > ''' + convert(nchar(36), @rowguid) + ''' '

if (@genlist is not null and rtrim(ltrim(@genlist)) <> '')
begin
if (@specified_article_only = 1)
begin
-- specify tablenick in the order by for performance reasons
execute (@selecttop + ' tablenick, rowguid, ' + @metadatacol + ', ts.type
from dbo.MSmerge_tombstone ts
where '
+ @generation_clause1 + @generation_clause2 + @generation_clause3 + ' and
tablenick = '
+ @tnstring + @rowguid_clause + '
order by ts.tablenick, ts.rowguid'
)

IF @@ERROR <>0
begin
return (1)
end

end
else
begin
exec sys.sp_MSget_article_processing_order @tablenick, @pubid, @last_art_processing_order output
select @last_art_processing_order_str = convert(varchar, @last_art_processing_order)

execute (@selecttop + ' tablenick, rowguid, ' + @metadatacol + ', ts.type
from dbo.MSmerge_tombstone ts, dbo.sysmergearticles sm
where '
+ @generation_clause1 + @generation_clause2 + @generation_clause3 + ' and
(
(tablenick = '
+ @tnstring + @rowguid_clause + ') or
(processing_order = '
+ @last_art_processing_order_str + ' and tablenick < ' + @tnstring + ') or
processing_order < '
+ @last_art_processing_order_str + '
)
and sm.pubid = '
+ @pubidstr + @tablenick_qual + '
and (ts.tablenick in
(select nickname from dbo.sysmergepartitioninfoview where pubid = '
+ @pubidstr + '
and logical_record_view is null)
or
ts.logical_record_parent_rowguid is null
)
order by processing_order desc, tablenick desc, rowguid asc'
)

IF @@ERROR <>0
begin
return (1)
end


end
end
end

return (0)

No comments:

Post a Comment

Total Pageviews