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