Tag: Replication

Querying SQL’s Distribution Database

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.

Example:

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
;

View on StackOverflow