CODEWITHSUNDEEP
sql-serverreplication
Randy Minder
Randy Minder

Reputation: 48452

T-SQL Query for Replication Articles

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?

Upvotes: 13

Views: 44903

Answers (6)

user9494276
user9494276

Reputation: 11

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

Upvotes: 1

andrews
andrews

Reputation: 2173

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.

Upvotes: 1

moudrick
moudrick

Reputation: 2346

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

Upvotes: 3

davmos
davmos

Reputation: 9577

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

Upvotes: 16

Helephant
Helephant

Reputation: 17028

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.

Upvotes: 6

Raj More
Raj More

Reputation: 48024

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.

Upvotes: 7

Related Questions