Sql-server – Sql Server : How to query list of subscriptions and Articles (for a given publication) from Publisher

sql serversql server 2014transactional-replication

In Sql Server 2014, how to query a list of Subscriptions & its articles for a given Publication from Publisher? (transactional replication)

Yes, there are easier ways to query from Distributor but I need a query from Publisher side.

Remote Distributor model (if it helps)

Thanks,

Best Answer

To get subscription list from a publisher database, you can run below tsql :

use publisherDB;
select  
db_name() PublisherDB 
, sp.name as PublisherName 
, sa.name as TableName 
, UPPER(srv.srvname) as SubscriberServerName 
from dbo.syspublications sp  
join dbo.sysarticles sa on sp.pubid = sa.pubid 
join dbo.syssubscriptions s on sa.artid = s.artid 
join master.dbo.sysservers srv on s.srvid = srv.srvid

Incase if someone needs for merge replication :

use publisherDB
go
select distinct 
    sa.name as articlename,
    sp.publisher as publisherserver,
    sp.publisher_db as publisherdb,
    sp.name as publicationname
    ,ss.subscriber_server as subscriberservername 
from dbo.sysmergearticles sa
join dbo.sysmergepublications sp on sa.pubid = sp.pubid
join dbo.sysmergesubscriptions ss on ss.pubid = sa.pubid
order by subscriberservername
Related Question