Transactional SQL Server Replication Toolbox Scripts

Transactional SQL Server Replication Toolbox Scripts

Originally published on KendalVanDyke.com.

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 in this Transactional SQL Server Replication Toolbox Scripts series.

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.

/********************************************************************************************* 
Transactional SQL Server Replication Toolbox Scripts: 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

 

About the Author

Microsoft SQL Server MVP & Principal Consultant

Kendal Van Dyke

UpSearch Alum Kendal Van Dyke is a database strategist, community advocate, public speaker and blogger. He is passionate about helping leaders use Microsoft's SQL Server to solve complex problems that protect, unlock and optimize data's value.

Since 1999, Kendal has specialized in SQL Server database management solutions and provided IT strategy consulting. Kendal excels at disaster recovery, high availability planning/implementation and debugging/troubleshooting mission critical SQL Server environments.

Kendal Van Dyke served the SQL Server community as Founder and President of MagicPass, the Orlando, FL based chapter of the Professional Association for SQL Server (PASS). In 2012, Kendal served as a member of the PASS Board of Directors.

Kendal remains active in the SQL Server community as a speaker and blogger. He teaches SQL Server enthusiast and technology leaders how to protect, unlock and optimize data’s value. Since 2008, Kendal has operated a SQL Server focused blog at http://www.kendalvandyke.com/.

Microsoft acknowledged Kendal for his support and outstanding contributions to the SQL Server community by awarding him Microsoft MVP (2011-15). Learn more about Kendal Van Dyke https://upsearch.com/kendal-van-dyke/.

About UpSearch

up-social-round

UpSearch is a leading Microsoft Gold Partner for organizations who rely on Microsoft’s Data Platforms, and its mission is to enable every leader to unlock data’s full potential.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *