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 

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .