Getting a list of the articles in a publication.

1

May 17, 2022 by Kenneth Fisher

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;

One thought on “Getting a list of the articles in a publication.

  1. […] Kenneth Fisher disturbs the slumber of the forces of replication: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,692 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: