Get Replication Details


SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested



Date: 6 Jun 2020


Get a list of replication Publications, Subscriptions and Articles

Must be run against the server hosting the Replication Distribution database

Useful for identifying databases/tables on the source server that might be involved in replication.

Further planning can then be undertaken to ensure that the relevant publications and/or subscriptions are also recreated on the migrated DB

NOTE: Replication publisher can only be +/- 2 SQL Server versions different from the publisher, so this can sometimes put a real spanner in the works :-)

GENERATE script on: n/a


** There is some additional code included below, which was created on SQL Server 2008 many years ago, and which gets a slightly different set of metadata about replication. Included here for completeness


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDIF EXISTS (SELECT 1 FROM master..sysdatabases WHERE name = 'Distribution')BEGIN-- Get the publication name based on article SELECT DISTINCTp.publication AS Publication_Name,srv.srvname AS Publication_Server,a.publisher_db AS Publication_Database,a.article AS Publication_Table_Name,ss.srvname AS Subscription_Server,s.subscriber_db AS Subscription_Database,a.destination_object AS Subscription_Table_Name,da.subscriber_login AS Subscription_Login, AS Distribution_Agent_Job_NameFROM Distribution..MSArticles aJOIN Distribution..MSpublications pON a.publication_id = p.publication_idJOIN Distribution..MSsubscriptions sON p.publication_id = s.publication_idJOIN master..sysservers ssON s.subscriber_id = ss.srvidJOIN master..sysservers srvON srv.srvid = p.publisher_idJOIN Distribution..MSdistribution_agents daON da.publisher_id = p.publisher_idAND da.subscriber_id = s.subscriber_idORDER BY 1,2,3END

Additional Code:

--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 DistributionGOSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED-- Get the publication name based on article SELECT DISTINCTsrv.srvname publication_server, a.publisher_db, p.publication publication_name, a.article, a.destination_object, ss.srvname subscription_server, s.subscriber_db, AS distribution_agent_job_nameFROM MSArticles aJOIN MSpublications p ON a.publication_id = p.publication_idJOIN MSsubscriptions s ON p.publication_id = s.publication_idJOIN master..sysservers ss ON s.subscriber_id = ss.srvidJOIN master..sysservers srv ON srv.srvid = p.publisher_idJOIN MSdistribution_agents da ON da.publisher_id = p.publisher_idAND da.subscriber_id = s.subscriber_idORDER 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 , as PublisherName , 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_replcationInfoELSESELECT DISTINCTPublisherDB,PublisherName,SubscriberServerNameFROM #tmp_replcationInfoDROP 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, publicationFROM dbo.MSreplication_subscriptionsORDER BY 1,2,3