T-SQL Query for Replication Articles

Question

Does anyone know of a query I could run that would tell me what articles, if any, in a target database, are associated with a transactional replication publication?

Answer 1

For those needing a quick snippet...

SELECT 
  msp.publication AS PublicationName,
  msa.publisher_db AS DatabaseName,
  msa.article AS ArticleName,
  msa.source_owner AS SchemaName,
  msa.source_object AS TableName
FROM distribution.dbo.MSarticles msa
JOIN distribution.dbo.MSpublications msp ON msa.publication_id = msp.publication_id
ORDER BY 
  msp.publication, 
  msa.article

Answer 2

Here are all tables involved with SQL Server Replication

http://msdn.microsoft.com/en-us/library/ms179855.aspx

Scroll down to the section for "Replication Tables in the Subscription Database" and you will find the tables for publications, subscriptions, schemas, articles and columns.

Answer 3

Look in syspublications to see a list of the publications set up on the publisher database. Hopefully you will be able to see one with a description that identifies the subscriber database you are interested in. Take note of the pubid of the publication you are interested in:

select * from syspublications

Then look in sysarticles to see which tables are actually replicated to that end point:

select * from sysarticles where pubid = 3

This needs to run on the publisher database, not the subscriber database.

Answer 4

EXEC sp_helparticle @publication='{your_publication_name}'

Displays information about an article. This stored procedure is executed at the Publisher on the publication database. For Oracle Publishers, this stored procedure is executed at the Distributor on any database.

See https://msdn.microsoft.com/en-us/library/ms187741.aspx

Answer 5

For those looking to list MERGE REPLICATION articles the following snippet may help:

SELECT 
    p.Name,
    a.* 
FROM [Publisher_DB_NAME].dbo.sysmergepublications p
JOIN [Publisher_DB_NAME].dbo.sysmergearticles a on p.pubid=a.pubid

Note, that sysmergepublications and sysmergearticles tables are queried in the Publisher DB because MSpublications and MSarticles tables in distribution DB mentioned in other answers in my case with SQL Server 2008 R2 had no records for merge replication articles.

Hope this helps someone working with merge replication who ended up here with SO search.

Answer 6

SELECT 
    p.Name,
    a.* 
FROM dbo.sysmergepublications p
JOIN dbo.[sysmergeextendedarticlesview] a on p.pubid=a.pubid 

Also you can use this to get the view list