--20160629_检测复制订阅进度
--SQLSERVER2012
--创建链接服务器ReadServer,WriteServer，它们分别链接发布服务器、订阅服务器
--修改数据库名称
--在分发服务器执行 USE [distribution] go with cte_pub as (select ma.publisher_db,mp.publication_id,mp.publication,ma.article_id,t.name table_name,six.rows from [WriteServer].[DatdabaseName].sys.tables t inner join [WriteServer].[DatdabaseName].sys.sysindexes six ON t.object_id=six.id INNER JOIN [distribution].dbo.MSarticles ma ON t.name=ma.article INNER JOIN [distribution].dbo.MSpublications mp ON ma.publication_id=mp.publication_id where 1=1 and six.indid<=1 --AND t.name LIKE 'war%' --过滤发布表条件放在此更高效 ), cte_repl as (SELECT ma.publication_id,mp.publication,ma.article_id,t.name table_name,six.rows FROM [ReadServer].[DatdabaseName].sys.tables t LEFT JOIN [ReadServer].[DatdabaseName].sys.sysindexes six ON t.object_id=six.id INNER JOIN [WriteServer].[distribution].dbo.MSarticles ma ON t.name=ma.destination_object INNER JOIN [distribution].dbo.MSpublications mp ON mp.publication_id=ma.publication_id INNER JOIN [ReadServer].[DatdabaseName].dbo.MSreplication_objects mo ON mo.article = ma.article AND mo.publication=mp.publication where 1=1 and six.indid<=1 and mo.object_name LIKE 'sp_MSins%' --AND t.name LIKE 'war%' --过滤订阅表条件放在此更高效 ) select t1.publisher_db 发布数据库,t1.publication 发布名称,t1.article_id,t1.table_name 表名,t1.rows 发布数量 ,t2.rows 订阅数量,t1.rows-t2.rows 差异,GETDATE() 当前时间 from cte_pub t1 full join cte_repl t2 on t1.publication=t2.publication AND t1.article_id=t2.article_id where 1=1 --AND t1.table_name LIKE 'fcc_%' --显示特定表 --AND t1.rows>0 AND t2.rows=0 --仅显示存在同步数据的 --AND t1.rows<>t2.rows --仅显示数据同步不一致的 ORDER by ABS(t1.rows-t2.rows) desc,t1.table_name