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_showpendingchanges(nvarchar @destination_server, nvarchar @publication
, nvarchar @article
, int @show_rows)
MetaData:
create procedure sys.sp_showpendingchanges @destination_server sysname = NULL, @publication sysname = NULL, @article sysname = NULL, @show_rows int = 0 -- by default don't show rowguids. as declare @current_destination_server sysname declare @destination_db_name sysname declare @is_dest_subscriber bit declare @pub_name sysname declare @pubid uniqueidentifier declare @sentgen bigint declare @partition_id int declare @article_name sysname declare @tablenick int declare @replnickname binary(6) declare @use_partition_groups smallint declare @pending_deletes int declare @pending_partition_deletes int declare @pending_ins_and_upd int declare @retcode int -- Security Check exec @retcode= sys.sp_MSreplcheck_publish if @@error <> 0 or @retcode <> 0 return (1) declare @result_table TABLE ( destination_server sysname, pub_name sysname, destination_db_name sysname, is_dest_subscriber bit, article_name sysname, pending_deletes int, pending_ins_and_upd int, is_delete int, rowguid uniqueidentifier) declare #serv_pub_art cursor local fast_forward for select sms.subscriber_server, smp.name, sms.db_name, sys.fn_MSmerge_islocalpubid(smp.pubid), sma.name, sma.nickname, smp.use_partition_groups, sms.sentgen, sms.replnickname, sms.partition_id from (sysmergepublications smp with (nolock) JOIN sysmergearticles sma with (nolock) ON smp.pubid = sma.pubid) JOIN sysmergesubscriptions sms with (nolock) ON smp.pubid = sms.pubid and db_name() <> sms.db_name order by sms.subscriber_server, smp.name, sma.name asc open #serv_pub_art fetch #serv_pub_art into @current_destination_server, @pub_name, @destination_db_name, @is_dest_subscriber, @article_name, @tablenick, @use_partition_groups, @sentgen, @replnickname, @partition_id while (@@fetch_status != -1) begin select @pending_deletes = 0 select @pending_ins_and_upd = 0 if (@destination_server IS NULL or @destination_server = @current_destination_server) and (@publication IS NULL or @publication = @pub_name) and (@article IS NULL or @article = @article_name) begin if @use_partition_groups = 1 and @partition_id IS NOT NULL begin -- Calculate pending deletes based on water marks -- select @pending_deletes = count(*) from (MSmerge_genhistory gh with (nolock) JOIN MSmerge_tombstone ts with (nolock) ON gh.generation = ts.generation) JOIN MSmerge_past_partition_mappings ppm with (nolock) ON ts.rowguid = ppm.rowguid and ts.generation = ppm.generation where gh.art_nick = @tablenick and gh.generation > @sentgen and ppm.partition_id = @partition_id and sys.fn_MSmerge_nicknamefound(gh.nicknames,@replnickname) = 0 -- Calculate pending partitiondeletes based on water marks -- select @pending_partition_deletes = count(*) from MSmerge_genhistory gh with (nolock) JOIN MSmerge_past_partition_mappings ppm with (nolock) ON gh.generation = ppm.generation LEFT OUTER JOIN MSmerge_tombstone ts with (nolock) ON ts.rowguid = ppm.rowguid where gh.art_nick = @tablenick and gh.generation > @sentgen and ts.rowguid is null and ppm.partition_id = @partition_id and sys.fn_MSmerge_nicknamefound(gh.nicknames,@replnickname) = 0 select @pending_deletes = @pending_deletes + @pending_partition_deletes if @show_rows = 1 begin insert into @result_table(destination_server,pub_name, destination_db_name, is_dest_subscriber,article_name, pending_deletes, pending_ins_and_upd, is_delete, rowguid) select @current_destination_server, @pub_name, @destination_db_name, @is_dest_subscriber, @article_name, 0, 0, 1, ts.rowguid from (MSmerge_genhistory gh with (nolock) JOIN MSmerge_tombstone ts with (nolock) ON gh.generation = ts.generation) JOIN MSmerge_past_partition_mappings ppm with (nolock) ON ts.rowguid = ppm.rowguid and ts.generation = ppm.generation where gh.art_nick = @tablenick and gh.generation > @sentgen and ppm.partition_id = @partition_id and sys.fn_MSmerge_nicknamefound(gh.nicknames,@replnickname) = 0 insert into @result_table(destination_server,pub_name, destination_db_name, is_dest_subscriber,article_name, pending_deletes, pending_ins_and_upd, is_delete, rowguid) select @current_destination_server, @pub_name, @destination_db_name, @is_dest_subscriber, @article_name, 0, 0, 1, ppm.rowguid from MSmerge_genhistory gh with (nolock) JOIN MSmerge_past_partition_mappings ppm with (nolock) ON gh.generation = ppm.generation LEFT OUTER JOIN MSmerge_tombstone ts with (nolock) ON ts.rowguid = ppm.rowguid where gh.art_nick = @tablenick and gh.generation > @sentgen and ts.rowguid is null and ppm.partition_id = @partition_id and sys.fn_MSmerge_nicknamefound(gh.nicknames,@replnickname) = 0 end -- Calculate pending updates and inserts based on water marks -- select @pending_ins_and_upd = count(*) from (MSmerge_genhistory gh with (nolock) JOIN MSmerge_contents mc with (nolock) ON gh.generation = mc.generation) JOIN MSmerge_current_partition_mappings cpm with (nolock) ON mc.rowguid = cpm.rowguid where gh.art_nick = @tablenick and gh.generation > @sentgen and cpm.partition_id = @partition_id and sys.fn_MSmerge_nicknamefound(gh.nicknames,@replnickname) = 0 if @show_rows = 1 begin insert into @result_table(destination_server,pub_name, destination_db_name, is_dest_subscriber,article_name, pending_deletes, pending_ins_and_upd, is_delete, rowguid) select @current_destination_server, @pub_name, @destination_db_name, @is_dest_subscriber, @article_name, 0,0,0, mc.rowguid from (MSmerge_genhistory gh with (nolock) JOIN MSmerge_contents mc with (nolock) ON gh.generation = mc.generation) JOIN MSmerge_current_partition_mappings cpm with (nolock) ON mc.rowguid = cpm.rowguid where gh.art_nick = @tablenick and gh.generation > @sentgen and cpm.partition_id = @partition_id and sys.fn_MSmerge_nicknamefound(gh.nicknames,@replnickname) = 0 end end else begin -- Calculate pending deletes based on water marks -- select @pending_deletes = count(*) from MSmerge_genhistory gh with (nolock) JOIN MSmerge_tombstone ts with (nolock) ON gh.generation = ts.generation where gh.art_nick = @tablenick and gh.generation > @sentgen and sys.fn_MSmerge_nicknamefound(gh.nicknames,@replnickname) = 0 if @show_rows = 1 begin insert into @result_table(destination_server,pub_name, destination_db_name, is_dest_subscriber,article_name, pending_deletes, pending_ins_and_upd, is_delete, rowguid) select @current_destination_server, @pub_name, @destination_db_name, @is_dest_subscriber, @article_name, 0, 0,1, ts.rowguid from MSmerge_genhistory gh with (nolock) JOIN MSmerge_tombstone ts with (nolock) ON gh.generation = ts.generation where gh.art_nick = @tablenick and gh.generation > @sentgen and sys.fn_MSmerge_nicknamefound(gh.nicknames,@replnickname) = 0 end -- Calculate pending updates and inserts based on water marks -- select @pending_ins_and_upd = count(*) from MSmerge_genhistory gh with (nolock) JOIN MSmerge_contents mc with (nolock) ON gh.generation = mc.generation where gh.art_nick = @tablenick and gh.generation > @sentgen and sys.fn_MSmerge_nicknamefound(gh.nicknames,@replnickname) = 0 if @show_rows = 1 begin insert into @result_table(destination_server,pub_name, destination_db_name, is_dest_subscriber,article_name, pending_deletes, pending_ins_and_upd, is_delete, rowguid) select @current_destination_server, @pub_name, @destination_db_name, @is_dest_subscriber, @article_name, 0, 0, 0, mc.rowguid from MSmerge_genhistory gh with (nolock) JOIN MSmerge_contents mc with (nolock) ON gh.generation = mc.generation where gh.art_nick = @tablenick and gh.generation > @sentgen and sys.fn_MSmerge_nicknamefound(gh.nicknames,@replnickname) = 0 end end if @show_rows = 0 begin insert into @result_table(destination_server,pub_name, destination_db_name, is_dest_subscriber,article_name, pending_deletes, pending_ins_and_upd) values (@current_destination_server, @pub_name, @destination_db_name, @is_dest_subscriber, @article_name, @pending_deletes, @pending_ins_and_upd) end end fetch #serv_pub_art into @current_destination_server, @pub_name, @destination_db_name, @is_dest_subscriber, @article_name, @tablenick, @use_partition_groups, @sentgen, @replnickname, @partition_id end close #serv_pub_art deallocate #serv_pub_art if @show_rows = 1 select destination_server,pub_name, destination_db_name, is_dest_subscriber,article_name, is_delete, rowguid from @result_table order by destination_server, pub_name, destination_db_name, article_name asc else select destination_server,pub_name, destination_db_name, is_dest_subscriber,article_name, pending_deletes, pending_ins_and_upd from @result_table order by destination_server, pub_name, destination_db_name, article_name asc return 0
No comments:
Post a Comment