Blog Post

Getting a list of the articles in a publication.

,

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;

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate