patternsqlMinor
Querying number of subscription to each publication
Viewed 0 times
numbereachqueryingpublicationsubscription
Problem
We have a production box that has about 60 publications on it. Some of these are used, and some seem to not be... What I need to do is create, or find, a script that will get the count of subscribers/subscriptions per publication so I can easily identify unused publications.
We are using SQL Server 2005. :(
Thank you,
Wes
We are using SQL Server 2005. :(
Thank you,
Wes
Solution
I was able to get the necessary info I needed by poking around a bit:
use Distribution;
select count(s.publisher_id) as 'Number of Subscribers', p.publication_id, p.Publisher_db, p.publication from MSpublications p
left outer join MSmerge_subscriptions s on p.publication_id = s.publication_id
group by p.publication_id, p.publisher_db, p.publication
Order by Publisher_db, Publication_id ascCode Snippets
use Distribution;
select count(s.publisher_id) as 'Number of Subscribers', p.publication_id, p.Publisher_db, p.publication from MSpublications p
left outer join MSmerge_subscriptions s on p.publication_id = s.publication_id
group by p.publication_id, p.publisher_db, p.publication
Order by Publisher_db, Publication_id ascContext
StackExchange Database Administrators Q#40235, answer score: 2
Revisions (0)
No revisions yet.