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


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

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 
    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
IF @Detail = 'Y' 
   SELECT * FROM #tmp_replcationInfo 
print 'nothing to display !! '
DROP TABLE #tmp_replcationInfo 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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