http://www.sqlservercentral.com/blogs/kendalvandyke/2013/01/29/transactional-replication-toolbox-scripts-show-articles-and-columns-for-all-publications/

Printed 2014/09/02 07:32PM

Transactional Replication Toolbox Scripts: Show Articles And Columns For All Publications

By Kendal Van Dyke, 2013/01/29

During the last few years I've worked extensively with transactional replication and have written a handful of scripts that have found a permanent home in my "useful scripts" toolbox. I've provided these scripts as downloads whenever I've presented about replication...but not everyone who has worked with replication has been to one of my presentations (or had access to the downloads afterwards) so I'm posting them here.

The first script in my toolbox shows all of the articles and columns in each article for all transactional publications in a published database. It's pretty straightforward - just execute the script in the published database on the publisher. Note that because it uses the FOR XML PATH directive it must be run on SQL 2005 or higher.

Here's the script:

/*********************************************************************************************
Transactional Replication Toolbox: Show Articles and Columns for All Publications

Description:
   Shows articles and columns for each article for all transactional publications

   (C) 2013, Kendal Van Dyke (mailto:kendal.vandyke@gmail.com)

Version History:
   v1.00 (2013-01-29)

License:
   This query is free to download and use for personal, educational, and internal
   corporate purposes, provided that this header is preserved. Redistribution or sale
   of this query, in whole or in part, is prohibited without the author's express
   written consent.

Note:
   Execute this query in the published database on the PUBLISHER

   Because this query uses FOR XML PATH('') it requires SQL 2005 or higher
  
*********************************************************************************************/

SELECT
  
syspublications.name AS "Publication",
   sysarticles.name
AS "Article",
  
STUFF(
       (
          
SELECT ', ' + syscolumns.name AS [text()]
          
FROM sysarticlecolumns WITH (NOLOCK)
              
INNER JOIN syscolumns WITH (NOLOCK) ON sysarticlecolumns.colid = syscolumns.colorder
          
WHERE sysarticlecolumns.artid = sysarticles.artid
              
AND sysarticles.objid = syscolumns.id
          
ORDER BY syscolumns.colorder
          
FOR XML PATH('')
       ),
1, 2, ''
  
) AS "Columns" FROM syspublications WITH (NOLOCK)
  
INNER JOIN sysarticles WITH (NOLOCK) ON syspublications.pubid = sysarticles.pubid
ORDER BY syspublications.name, sysarticles.name


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.