SQL Server Replication Configuration Scripts

Problem

My company uses SQL Server replication for moving data between servers and we have several publications with many articles being replicated. Therefore, I get questions all of the time about what tables are being replicated, what databases are published, what servers are subscribers, etc... This information can be pulled using SQL Server Management Studio, but it takes a long time to do manually since there are so many properties for each publication. In this tip I will show you three basic scripts that I use to help answer these questions.

Solution

The scripts can be run from various databases to pull this information and if you have multiple servers for replication like we do, you can use the Multi Server Query feature to query several servers at one time. See the Execute same SQL Server query across multiple servers at the same time using Central Management Servers article for more information.

These scripts work for SQL 2005 and SQL 2008.

General Overview

I have three different scripts that you can use:

Script to run on Distribution database - This script returns completed setup replication information.
Script to run on Publisher database - This script returns what publications have been setup.
Script to run on Subscriber database - This script returns what article(s) is/are being replicated to the subscriber database. I also use this to find any orphaned subscribers.

Script to run on Distribution database

This script returns completed setup replication information. Unless an orphan article exists, this will return a complete set of replication information. I also added the distribution agent job name to show how easy it is to pull in other configuration information. I recommend making this a stored procedure and then creating a Reporting Services report, so that anyone can easily access this data.

USE Distribution 
GO 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
-- Get the publication name based on article 
SELECT DISTINCT  
srv.srvname publication_server  
, a.publisher_db 
, p.publication publication_name 
, a.article 
, a.destination_object 
, ss.srvname subscription_server 
, s.subscriber_db 
, da.name AS distribution_agent_job_name 
FROM MSArticles a  
JOIN MSpublications p ON a.publication_id = p.publication_id 
JOIN MSsubscriptions s ON p.publication_id = s.publication_id 
JOIN master..sysservers ss ON s.subscriber_id = ss.srvid 
JOIN master..sysservers srv ON srv.srvid = p.publisher_id 
JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id  
     AND da.subscriber_id = s.subscriber_id 
ORDER BY 1,2,3

Script to run on Publisher database

This script returns what publications have been setup. This will go through all the published databases and return information if the database has replication enabled. Sometimes, I just want to see the publication name and subscriber server names (no articles) to see what servers are being used with replication other times I want all of the information, so I added a variable called @Detail and if you set @Detail = 'Y' it will return data with the article list. Any other value will only return the publisherDB, publisherName and SubscriberServerName

-- Run from Publisher Database  
-- Get information for all databases 
DECLARE @Detail CHAR(1) 
SET @Detail = 'Y' 
CREATE TABLE #tmp_replcationInfo ( 
PublisherDB VARCHAR(128),  
PublisherName VARCHAR(128), 
TableName VARCHAR(128), 
SubscriberServerName VARCHAR(128), 
) 
EXEC sp_msforeachdb  
'use ?; 
IF DATABASEPROPERTYEX ( db_name() , ''IsPublished'' ) = 1 
insert into #tmp_replcationInfo 
select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid 
' 
IF @Detail = 'Y' 
   SELECT * FROM #tmp_replcationInfo 
ELSE 
SELECT DISTINCT  
PublisherDB 
,PublisherName 
,SubscriberServerName  
FROM #tmp_replcationInfo 
DROP TABLE #tmp_replcationInfo

Script to run on Subscriber database

This script returns what article(s) is/are being replicated to the subscriber database. I also use this to find orphaned subscribers. This is rather simple since there is not much information to pull.

-- Run from Subscriber Database 
SELECT distinct publisher, publisher_db, publication
FROM dbo.MSreplication_subscriptions
ORDER BY 1,2,3

Next Steps

These are basic scripts that you can use to start and then begin to add more information as needed.
You can use a Multi Server Query to pull information from multiple servers at one time.
Think about exposing the data from the queries using Reporting Servers reports so developers can access this data easily
You can also get additional related information like replication history by adding a few more columns to the output.
Check out these other replication tips

About the author

Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2009-08-04

SQL Server Replication Scripts to get Replication Configuration Information