Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

[replication] all articles and server information

Posted by Simon Cho on 03/13/2012

use distribution
go

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s