搜尋本站文章

2017-11-30

[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

[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