When trying to query what tables are being replicated most people know about the
is_replicated flag in
sys.tables, however, fewer know that there is a lot more information available about replication in the
distribution database. With the
distribution database it is possible to query what Articles (tables/views/objects etc.) are published and which Publications they are available in.
SELECT [P].[publication] AS [Publication Name] ,[A].[publisher_db] AS [Database Name] ,[A].[article] AS [Article Name] ,[A].[source_owner] AS [Schema] ,[A].[source_object] AS [Object] FROM [distribution].[dbo].[MSarticles] AS [A] INNER JOIN [distribution].[dbo].[MSpublications] AS [P] ON ([A].[publication_id] = [P].[publication_id]) ORDER BY [Publication Name] ASC ,[Article Name] ASC ;