Take the 2-minute tour ×
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. It's 100% free, no registration required.

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? msdn.microsoft.com/en-us/library/ms188905(v=sql.110).aspx –  Thomas Stringer Apr 17 '13 at 14:56
    
Yes, it was.... –  Wes Crockett Apr 17 '13 at 18:20
add comment

2 Answers

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 
ELSE 
print 'nothing to display !! '
DROP TABLE #tmp_replcationInfo 
share|improve this answer
add comment

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
add comment

Your Answer

 
discard

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.