Category: SQL Server
How to know which tables are replicated?
Sometimes you need to know all the tables that are replicated in a DB, for that use this scripts, run it in the replicated DB:
--Which tables are published for Merge Replication and his subscribers
SELECT
b.name AS [Table],
DB_NAME() AS PublisherDB,
a.subscriber_server AS Subscriber,
a.db_name AS SubscriberDB
from sysmergesubscriptions a, sysmergearticles b
where a.pubid=b.pubid
order by a.subscriber_server
--Which tables are published for Transactional Replication and his subscribers
SELECT
a.name AS [Table],
is_published AS is_tran_published,
DB_NAME() AS PublisherDB,
c.srvname AS Subscriber,
c.dest_db AS SubscriberDB
FROM sys.tables a
INNER JOIN dbo.sysarticles b ON a.object_id = b.objid
INNER JOIN dbo.syssubscriptions c ON b.artid = c.artid
WHERE is_published = 1
Consultor Senior SQL Server & BI con 9 años de experiencia, MCSE Data Platform con conocimientos de toda la herramienta y enfocado principalmente a la detección y mejora de problemas de rendimiento en Base de Datos. En mi tiempo libre soy un gran aficionado a la fotografía de estilo urbano y de lugares abandonados.
