[SQL Server]: Query Replication Configuration - Analysis Publication and Article for each Distributor

延續前一篇：[SQL Server]: Query Replication Configuration - Get completed setup replication information


Analysis Publication and Article for each Distributor

• 分析彙總 Publication 與 Article 的相關資訊。
• 若 Distributor 有多個 Distribution Database，此 Script 可完整取得各個 Distribution Database 的相關資訊。

-- 112_Analysis Publication and Article for each Distributor

連線到 Distributor，執行以下 T-SQL Script:

-- EX2. Analysis Publication and Article for each Distributor
-- Connect to Distributor, for example: Dist01
USE master
DECLARE @tsql varchar(4000) 

SET @tsql = 'IF ''?'' IN((SELECT name FROM sys.databases WHERE is_distributor=1)) 
BEGIN
 USE [?]
 SELECT @@SERVERNAME Distributor, DB_NAME() Distribution,
 pu.publication Publication, COUNT(*) ObjectCount
 FROM MSarticles ar INNER JOIN MSpublications pu ON pu.publication_id = ar.publication_id
 GROUP BY pu.publication
END';

EXEC sp_MSforeachdb @tsql;
GO

---

範例程式碼

20171129_Query Replication Configuration
https://drive.google.com/drive/folders/1H2hyxRDlfuMzdgJmnSAwlg6P6MsuN_3O?usp=sharing

---

參考資料

[SQL Server]: Query Replication Configuration - Get completed setup replication information
http://sharedderrick.blogspot.tw/2017/11/sql-server-query-replication.html

MSpublications (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mspublications-transact-sql

MSsubscriptions (Transact-SQL)
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/mssubscriptions-transact-sql