The other day I was asked to supply a list of all of the tables being replicated into a given database. Now, for those of you that aren’t aware, if I replicate a group of tables from database SourceDB into DestDB I can still have additional tables in DestDB that have nothing to do with the replication. So this wasn’t just a matter of getting a list of tables from the database.
I did a little searching around and came across this post in StackExchange. It has a nice little query to produce the data I needed and I wanted to share. I’ve made a few changes to the queries the poster used because of a few issues I have in my system. As time goes by I may add to these, we will see.
First things first, a quick query to get all of the databases on your server that have a publication.
SELECT * FROM sys.databases
WHERE is_published = 1 or is_merge_published = 1;
Next, for the databases with transactional replication (is_published = 1) use the following query.
USE publisherDB; /* This is the database where the publication exists. */SELECT
DB_Name() PublicationDB
, sp.name AS PublicationName
, sp.status AS PublicationActive
, sa.name AS ArticleName
, o.name AS ObjectName
, srv.srvname AS SubscriberServerName
, s.dest_db AS SubscriberDBName
FROM dbo.syspublications sp
JOIN dbo.sysarticles sa ON sp.pubid = sa.pubid
LEFT OUTER JOIN dbo.syssubscriptions s ON sa.artid = s.artid
LEFT OUTER JOIN master.dbo.sysservers srv ON s.srvid = srv.srvid
JOIN sys.objects o ON sa.objid = o.object_id;
You’ll notice the left outer joins on the subscription section. That’s because we are using AWS DMS in my office, which requires a publication, but doesn’t actually have a subscription.
For the databases with merge replication (is_merge_published = 1) use the following query.
USE publisherDB; /* This is the database where the publication exists. */SELECT DISTINCT
sp.publisher AS PublisherServer
, sp.publisher_db AS PublicationDB
, sp.name AS PublicationName
, sa.name AS ArticleName
, o.name AS ObjectName
, ss.subscriber_server AS SubscriberServerName
, s.dest_db AS SubscriberDBName
FROM dbo.sysmergearticles sa
JOIN dbo.sysmergepublications sp on sa.pubid = sp.pubid
LEFT OUTER JOIN JOIN dbo.sysmergesubscriptions ss on ss.pubid = sa.pubid
JOIN sys.objects o ON sa.objid = o.object_id;