info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



SQL Server Transactional Replication - Get all Publication Article Columns, Get all Publication Article Filtered Rows
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2019-02-15








--Execute in each Published Database:
--Get all Article Columns Included:

SELECT
@@SERVERNAME as 'Publisher',
DB_Name() as 'Publisher Database',
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;


--Execute in each Published Database:
--Get all Article Filtered Rows:

Select
@@SERVERNAME as 'Publisher',
DB_Name() as 'Publisher Database',
t2.name as 'Publication', UPPER(t1.Name) as 'Article', t1.filter_clause as 'Row Filter'
from sysarticles t1
inner join syspublications t2 on t1.pubid = t2.pubid
where
t1.filter_clause is not null