Getting a list of the articles in a publication.
5May 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 dbo.sysmergesubscriptions ss on ss.pubid = sa.pubid
JOIN sys.objects o ON sa.objid = o.object_id;
SQL Studies 
[…] Kenneth Fisher disturbs the slumber of the forces of replication: […]
Last query more like:
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
, ss.db_name AS SubscriberDBName
FROM dbo.sysmergearticles sa
JOIN dbo.sysmergepublications sp on sa.pubid = sp.pubid
LEFT OUTER JOIN dbo.sysmergesubscriptions ss on ss.pubid = sa.pubid
JOIN sys.objects o ON sa.objid = o.object_id;
GO
I’ve corrected the code given above in the comment…
USE [db_name] /* This is the database where the publication exists. */
SELECT DISTINCT
sp.name AS PublicationName
, sa.name AS ArticleName
, o.name AS ObjectName
, ss.srvname AS SubscriberServerName
, ss.dest_db AS SubscriberDBName
FROM dbo.sysarticles sa
JOIN dbo.syspublications sp on sa.pubid = sp.pubid
LEFT OUTER JOIN dbo.syssubscriptions ss on ss.artid = sa.artid
JOIN sys.objects o ON sa.objid = o.object_id
It’s possible that Microsoft made a schema change on these tables in between versions, but the above snippet I posted will at least work on 2019, and achieves what the query in the blog is attempting to do
Also, I changed the referenced tables as the ..merge.. tables did not apply for my situation, there are different kinds of replication so it’s possible that for others they will need to use the ..merge.. tables instead