Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL Server- get replication articles from a given published database. See https://natethedba.wordpress.com/tag/tsql2sday/. Replace [dbName] at top with your published database name. Subject to update and open to feedback!
USE [dbName];
SELECT pdb.publisher_db [DB], objs.[type] AS [ObjType], art.destination_object AS [Name], pub.publication [Pub], objs.[PubType]
FROM [distribution].dbo.MSpublisher_databases pdb with (nolock)
JOIN [distribution].dbo.MSarticles art with (nolock)
ON art.[publisher_db] = pdb.[publisher_db]
JOIN [distribution].dbo.MSpublications pub with (nolock)
on pub.[publication_id] = art.[publication_id]
and pub.[publisher_db] = pdb.[publisher_db]
LEFT JOIN (
SELECT 'table' AS [type], name
, (CASE WHEN tab.is_published = 1 THEN 'Trans'
WHEN tab.is_merge_published = 1 THEN 'Merge'
WHEN tab.is_replicated = 1 AND tab.is_published = 0 THEN 'Snap'
ELSE '<Unknown>' END) AS PubType
FROM sys.tables tab
WHERE tab.is_published = 1 OR tab.is_schema_published = 1
OR tab.is_merge_published = 1 OR tab.is_replicated = 1
UNION
SELECT 'view' AS [type], name, 'Schema' AS PubType
FROM sys.views vws
WHERE vws.is_schema_published = 1
UNION
SELECT 'proc' AS [type], name, 'Schema' AS PubType
FROM sys.procedures procs
WHERE procs.is_schema_published = 1
UNION
SELECT 'func' AS [type], name, 'Schema' AS PubType
FROM sys.objects obj
WHERE obj.type_desc LIKE '%func%'
AND obj.is_schema_published = 1
) objs
ON art.source_object = objs.name
ORDER BY art.[publisher_db], objs.[type], art.article
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.