Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for March 13th, 2012

[replication] all articles and server information

Posted by Simon Cho on 03/13/2012

use distribution

select distinct 
@@SERVERNAME as Distributor
, db_name(db_id()) as Distributor_DB
, srv.srvname publication_server 
, a.publisher_db
, p.publication publication_name
, a.article
, a.destination_object
, p.retention
, ss.srvname subscription_server
, s.subscriber_db
from MSArticles a 
join MSpublications p on a.publication_id = p.publication_id
join MSsubscriptions s on p.publication_id = s.publication_id
join master..sysservers ss on s.subscriber_id = ss.srvid
join master..sysservers srv on srv.srvid = p.publisher_id
join MSdistribution_agents da on da.publisher_id = p.publisher_id 
and da.subscriber_id = s.subscriber_id
ORDER BY p.retention

Posted in Common | Tagged: | Leave a Comment »