Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
Is MSmerge_subscriptions what you're looking for? – Thomas Stringer Apr 17 '13 at 14:56
Yes, it was.... – Wes Crockett Apr 17 '13 at 18:20

Below script will give you information for all databases with replication info:

-- For Merge replication
-- Author: Kin Shah

DECLARE @Detail CHAR(1) 
SET @Detail = 'Y' 
CREATE TABLE #tmp_replcationInfo 
    articlename VARCHAR(128),  
    publisherserver VARCHAR(128), 
    publisherdb VARCHAR(128), 
    publicationname VARCHAR(128),
    subscriberservername VARCHAR(128) 
EXEC sp_msforeachdb  
'use ?; 
IF DATABASEPROPERTYEX ( db_name() , ''IsMergePublished'' ) = 1 
insert into #tmp_replcationInfo 
select distinct as articlename,
    sp.publisher as publisherserver,
    sp.publisher_db as publisherdb, 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
IF @Detail = 'Y' 
   SELECT * FROM #tmp_replcationInfo 
print 'nothing to display !! '
DROP TABLE #tmp_replcationInfo 
share|improve this answer

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 asc
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.