Replication Publication Articles

This is a useful script when planning where to place a new publication. I like grouping common articles into one publication to make for easier administration . I also like knowing the row count for those articles.

DECLARESearchStr NVARCHAR(100)
SET @SearchStr = '%Orders%' ;

SELECT p.name AS PublicationName ,
       a.name AS ArticalName ,
       t.row_count AS RowCnt
FROM   dbo.syspublications p
       INNER JOIN dbo.sysarticles a
           ON p.pubid = a.pubid
       LEFT JOIN sys.dm_db_partition_stats t
           ON a.objid = t.object_id
WHERE t.index_id < 2
       AND p.name LIKE @SearchStr
       AND a.name LIKE @SearchStr
ORDER BY p.name , a.name ;

Download the file here.

This is a useful script when planning where to place a new publication. I like grouping common articles into one publication to make for easier administration. I also like knowing the row count for those articles. I developed this simple script to help me consolidate replication articles into fewer publications. In this case, there was one publication per article, and there were a lot of articles (yikes). All publications where push subscriptions. This made the production database do all the work.