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)
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
Found this sys proc, not sure if it is correct.
Any ideas for better solution?