[SQL Server]: Query Replication Configuration - Get Publication, Article and PublicationType information

延續前一篇：[SQL Server]: Query Replication Configuration - Analysis Publication and Article for each Distributor

Get Publication, Article and PublicationType information

• 查詢 PublisherDB, SubscriberDB, Publication,  Article, Distributor, Distribution, PublicationType 等相關資訊。
• 若 Distributor 有多個 Distribution Database，此 Script 可完整取得各個 Distribution Database 的相關資訊。

-- 113_Get Publication, Article and PublicationType information

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

-- EX3. Get Publication, Article and PublicationType information
-- PublisherDB, SubscriberDB, Publication,  Article, Distributor, Distribution, PublicationType
-- 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 sc.publisher_db PublisherDB, sc.subscriber_db SubscriberDB, pu.publication Publication,
 ar.source_owner [Schema] ,ar.article Article,
 @@SERVERNAME Distributor, DB_NAME() DistributorDB, 
 CASE publication_type 
  WHEN 0 THEN ''Transactional''
  WHEN 1 THEN ''Snapshot''
  WHEN 2 THEN ''Merge''
 END PublicationType
FROM MSsubscriptions sc INNER JOIN MSarticles ar ON sc.article_id = ar.article_id
 INNER JOIN MSpublications pu ON pu.publication_id = sc.publication_id
ORDER 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 - Analysis Publication and Article for each Distributor
http://sharedderrick.blogspot.tw/2017/11/sql-server-query-replication_30.html

[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