Simon's SQL

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