Useful SQL scripts to extract replication metadata about articles and columns

Show Articles And Columns For All Publications

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helpsubscription-transact-sql?view=sql-server-ver15

Useful PowerShell code to capture sp_helpsubscription output into a table

invoke-sqlcmd -serverinstance PublicationServer -database 'PublicationDatabase' -query sp_helpsubscription -OutputAs DataTables |             
Write-SqlTableData -ServerInstance PublicationServer -DatabaseName DBAMonitoringDB -SchemaName dbo -TableName PublicationDatabasesubscriptions -Force

SQL to extract replication articles information

USE DBAMonitoringDB
GO

select @@SERVERNAME source_server, p.publication publication_name, 'PublicationDatabase' source_database_name, s.article source_table_name,p.Columns source_list_of_columns, 
subscriber destination_server, [destination database], s.article destination_table_name,p.Columns 
destination_list_of_columns  from PublicationDatabasepublications p 
join PublicationDatabasesubscriptions s on s.publication=p.publication and p.article=s.article

PublicationDatabasepublications table extracted through below SQL in above URL Show Articles And Columns For All Publications

USE 'PublicationDatabase'


GO

SELECT syspublications.NAME                AS "Publication",        sysarticles.NAME                    AS "Article",        Stuff((SELECT ', ' + syscolumns.NAME AS [text()]               FROM   sysarticlecolumns WITH (nolock)                      INNER JOIN syscolumns WITH (nolock)                              ON sysarticlecolumns.colid = syscolumns.colorder               WHERE  sysarticlecolumns.artid = sysarticles.artid                      AND sysarticles.objid = syscolumns.id               ORDER  BY syscolumns.colorder               FOR xml path('')), 1, 2, '') AS "Columns" FROM   syspublications WITH (nolock)        INNER JOIN sysarticles WITH (nolock)                ON syspublications.pubid = sysarticles.pubid ORDER  BY syspublications.NAME,           sysarticles.NAME;

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s