Friday, 6 February 2015

Find out the replication properties of a table

The script below lists the publisher, subscriber and publication for a replicated table in SQL Server. It is executed against the distribution database.

use distribution
go

-- View the replication properties of a table
select a.publisher_db, s.subscriber_db, a.article, p.publication, p.publication_type
from MSarticles a 
inner join MSpublications p on a.publication_id = p.publication_id
inner join MSsubscriptions s ON p.publication_id = s.publication_id and s.article_id = a.article_id
inner join master..sysservers ss ON s.subscriber_id = ss.srvid 
inner join master..sysservers srv ON srv.srvid = p.publisher_id 
inner join MSdistribution_agents da ON da.publisher_id = p.publisher_id  AND da.subscriber_id = s.subscriber_id and da.publication = p.publication
where a.destination_object like '%discountusage%'

No comments:

Post a comment