Monday, October 08, 2007

Query replicated articles

Query replicated articles
If you are using an environment where there many publications and subscriptions it is difficult to find out the list of articles for particular subscriber.

The following code uses system tables in distribution to accomplish the task.
This query will retrieve publisher, publication, subscriber database, subsriber_id

select distinct pub.Publisher_db,pub.Publication, sub.subscriber_db,
sub.subscriber_id,art.article, art.destination_object from
distrib_.dbo.MSsubscriptions sub
join .dbo.MSPublications pub
on sub.Publication_id = pub.Publication_id
join .dbo.MSArticles art
on art.publication_id = pub.publication_id
where sub.subscriber_id = [subsriber_id]

If you have multiple distribution databases on the same server you can use union all with pre-fix of database name.

No comments: