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;