SQL Server Replication Scripts: Show All Transactional Publications & Subscribers At Distributor

SQL Server Replication Scripts: Show All Transactional Publications & Subscribers At DistributorOriginally published on KendalVanDyke.com.

Anybody who has talked with me about replication or heard me present about it knows that I recommend using a dedicated remote distributor for anything beyond light replication workloads. Unfortunately neither SSMS nor Replication Monitor provide an easy “one view to rule them all” way at the distributor (or anywhere else) to show every transactional publication, subscriber, and article they’re subscribed to. The only way to gather that information using SSMS is to script out each publication and visually parse the SQL Server replication scripts. I manage hundreds of publications & subscriptions and that’s not a reasonable option for me so I’ve written SQL Server Replication Scripts: Show All Transactional Publications & Subscribers At Distributor to show me everything at once:

-- Show Transactional Publications and Subscriptions to articles at Distributor
-- Run this on the DISTRIBUTOR
-- Add a WHERE clause to limit results to one publisher\subscriber\publication\etc
SELECT  publishers.srvname AS [Publisher] ,
        publications.publisher_db AS [Publisher DB] ,
        publications.publication AS [Publication] ,
        subscribers.srvname AS [Subscriber] ,
        subscriptions.subscriber_db AS [Subscriber DB] ,
        articles.article AS [Article]
FROM    sys.sysservers AS publishers
        INNER JOIN distribution.dbo.MSarticles AS articles ON publishers.srvid = articles.publisher_id
        INNER JOIN distribution.dbo.MSpublications AS publications ON articles.publisher_id =publications.publisher_id
                                                              AND articles.publication_id =publications.publication_id
        INNER JOIN distribution.dbo.MSsubscriptions AS subscriptions ON articles.publisher_id =subscriptions.publisher_id
                                                              AND articles.publication_id =subscriptions.publication_id
                                                              AND articles.article_id = subscriptions.article_id
        INNER JOIN sys.sysservers AS subscribers ON subscriptions.subscriber_id = subscribers.srvid 

-- Limit results to subscriber
--WHERE   subscribers.srvname = '[Subscriber Server Name]' 

---- Limit results to publisher and publication
--WHERE   publishers.srvname = '[Publisher Server Name]'
--        AND MSpublications.publication = '[Publication Name]' 

ORDER BY publishers.srvname ,
        subscribers.srvname ,
        publications.publication ,

This script also works for distributors running SQL 2000; just substitute master.dbo.sysservers in place of sys.sysservers.


About the Author

Microsoft SQL Server MVP & Principal Consultant

Kendal Van Dyke

UpSearch Alum Kendal Van Dyke is a database strategist, community advocate, public speaker and blogger. He is passionate about helping leaders use Microsoft's SQL Server to solve complex problems that protect, unlock and optimize data's value.

Since 1999, Kendal has specialized in SQL Server database management solutions and provided IT strategy consulting. Kendal excels at disaster recovery, high availability planning/implementation and debugging/troubleshooting mission critical SQL Server environments.

Kendal Van Dyke served the SQL Server community as Founder and President of MagicPass, the Orlando, FL based chapter of the Professional Association for SQL Server (PASS). In 2012, Kendal served as a member of the PASS Board of Directors.

Kendal remains active in the SQL Server community as a speaker and blogger. He teaches SQL Server enthusiast and technology leaders how to protect, unlock and optimize data’s value. Since 2008, Kendal has operated a SQL Server focused blog at http://www.kendalvandyke.com/.

Microsoft acknowledged Kendal for his support and outstanding contributions to the SQL Server community by awarding him Microsoft MVP (2011-15). Learn more about Kendal Van Dyke https://upsearch.com/kendal-van-dyke/.

SQL Server Health Check Series

This blog series will help you decide if UpSearch’s <span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/microsoft-sql-server-health-check/” target=”_blank”>SQL Server Health Check</a></span> is right for your organization. Follow the links below to learn how you can use our assessment to identify and resolve SQL Server problems.

<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-introduction/” target=”_blank”>SQL Server Health Check Introduction</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-benefits/” target=”_blank”>SQL Server Health Check Benefits</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-1-collect-metrics/” target=”_blank”>SQL Server Health Check Process – Step 1</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-2-analyze-results/” target=”_blank”>SQL Server Health Check Process – Step 2</a></span></li>
<li><span style=”color: #0000ff;”><a style=”color: #0000ff;” href=”https://upsearch.com/sql-server-health-check-step-3-report-findings/” target=”_blank”>SQL Server Health Check Process – Step 3</a></span></li>


About UpSearch


UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.