This will ultimately need tidying up. You can run this query on the distribution node in a SQL replication environment. It’ll give you all the publisher servers and databases, along with the subscriber servers and databases. It will also give you the article count.

Some notes on the code

First, I think this is pretty cool. I added the article count so I can quickly see if there are differences between duplicate publications to either the same subscriber or another. It gives a easy ‘catch’ to check for slightly different publications if you need them to be the same.

Second, and the reason why I’m posting this is I was trying to find the server name for the publisher and subscriber. The internet told me to JOIN the sys.servers table, which gives you the *wrong* data. Instead it’s the MSreplservers table in the distribution database itself. Hey I found this interesting as it caused me to waste time trying to figure it out, hence the post.

SELECT
									a.c AS 'article_count',
									s.subscriber_id,
									ss1.srvname,
									p.publisher_db AS publisher_server,
									p.publication,
									ss2.srvname AS subscriber_server,
									s.subscriber_db,
									da.name AS job_name 
				FROM				MSpublications p 
								--ON	a.publication_id = p.publication_id 
							JOIN	MSsubscriptions s 
								ON	p.publication_id = s.publication_id 
							JOIN	MSdistribution_agents da 
								ON	da.publisher_id = p.publisher_id  
								AND da.subscriber_id = s.subscriber_id
							JOIN	MSreplservers ss1
								ON	p.publisher_id = ss1.srvid
							JOIN	MSreplservers ss2
								ON	s.subscriber_id = ss2.srvid
							CROSS APPLY (SELECT COUNT(*) as c, publication_id FROM MSArticles WHERE s.publication_ID = publication_id GROUP BY publication_id) a
				GROUP BY			p.publisher_db,
									p.publication,
									s.subscriber_db,
									da.name,
									a.c,
									ss1.srvname,
									ss2.srvname,
									s.subscriber_id