Connect With MyTechMantra.com









"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."




SQL Server Replication Query to find all tables involved with Replication


August 25, 2014


Introduction

As DBA there might be scenarios where you might find the need to identify to which SQL Server Publications a specific table belong too. It can be because the subscribers have informed that they are missing newer records, or there may be a need to rename the table and you want to make sure it is not part of replication. The TSQL scripts mentioned in the article works on SQL Server 2005 and higher versions.

Execute the below TSQL query against the Distribution database to find all the tables involved within SQL Server Replication. The query output Publisher Name, Publisher Database Name, Article Name, Schema Name and Table Name.

TSQL Query to Find Publication Name to which a Table belongs in SQL Server

Use distribution
GO

DECLARE @ArticleName SysName
SET @ArticleName = '<Table Name>'

SELECT
     MSP.publication AS 'Publication Name'
    ,MSA.publisher_db AS 'Database Name'
    ,MSA.article AS 'Article Name'
    ,MSA.source_owner AS 'Schema Name'
    ,MSA.source_object AS 'Table Name'
FROM
    DBO.MSarticles AS MSA
INNER JOIN DBO.MSpublications AS MSP
        ON MSA.publication_id = MSP.publication_id
    WHERE MSA.article = @ArticleName
        ORDER BY
            MSP.publication, MSA.article;
GO

TSQL Query to List all Articles which are Part of SQL Server Publications

Use distribution
GO

SELECT
     MSP.publication AS 'Publication Name'
    ,MSA.publisher_db AS 'Database Name'
    ,MSA.article AS 'Article Name'
    ,MSA.source_owner AS 'Schema Name'
    ,MSA.source_object AS 'Table Name'
FROM
    DBO.MSarticles AS MSA
        INNER JOIN DBO.MSpublications AS MSP
            ON MSA.publication_id = MSP.publication_id
        ORDER BY
            MSP.publication, MSA.article;
GO


SQL Server Replication Query to find all tables involved with Replication

Share this Article

LEARN MORE...
 
Winners
White Papers
Product Reviews
Trending News
All Articles
Free Tools
 
Follow Us...

Related Articles…