List of all replicated objects from Publisher DB

The below query will list the objects that are being replicated as well as the subscriber details. This query must be run on the publisher DB. To find the list of published databases on the server, we can use the below query:

Select Name from master.sys.databases where is_published =1

Now, we can find the list of objects from the published Database..

USE [PushRepl]
GO
With ReplicationObjects as
(Select pubid,artID,dest_object,dest_owner,objid,name from sysschemaarticles
union
Select pubid,artID,dest_table,dest_owner,objid,name from sysarticles)

Select Serverproperty('ServerName') as [PublisherServer],
B.name as [PublisherName],DB_Name() as [PublisherDB],
E.Name+'.'+A.Name as [PublisherTableName],D.Type_desc,
A.dest_owner+'.'+A.dest_Object as [SubscriberTableName],
C.dest_db as [SubscriberDB],C.srvname as [SubscriberServer]
From ReplicationObjects A
Inner Join syspublications B on A.pubid=B.pubid
Inner Join dbo.syssubscriptions C on C.artid=A.artid
Inner Join sys.objects D on A.objid=D.Object_id
Inner Join sys.schemas E on E.Schema_id=D.Schema_id
Where dest_db not in ('Virtual')

sample output(I removed couple columns in the pic because the pic is not showing up correctly.):

Advertisement