Finding Replication Publications For A Table

If you want to drop or rename a table, you will need to make ensure the table is not an article of a publication. If it is article for any publication, you need to find out what are those publications. If you have several publications and if one table is an article for many publications, it is not an easy task to check publications one by one.

The below query can be executed against the publisher database by providing the table name in the where clause. This query outputs the Publication Name, Source Table Name and the Destination Table.

SELECT P.name Publication,
A.name TableName,
A.dest_table DestinationTable FROM
syspublications P INNER JOIN
sysarticles A ON P.pubid = A.pubid
WHERE p.name ='TableName'



Related Articles :

2 Responses to “Finding Replication Publications For A Table”

  1. Not quite… p.name = ” not table name.

    Very helpful though. Thanks!

  2. I believe there are two “technicality” errors in your query:


    A.name ArtileName,

    WHERE A.objid = OBJECT_ID( ‘TableName’ )

    The original script probably works most of the time because by default the article name is the same as the object name, but it doesn’t have to be.

    Also, the target table name doesn’t have to be the same as the source table. To do that lookup, use:

    WHERE dest_table = ‘TableName’
    AND dest_owner = ‘dbo’ — schema name

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |