Find Transactional Replication Details

This will be a quick post about a script to find replication metadata in details.  You need to run this in your distributor (where distribution database reside) server and filter in various ways depending on your need.  Also modify order by based on what you are looking for.

Run where distribution database reside

USE distribution;
GO
SELECT DISTINCT
        p.publication ,
        p.publisher_db ,
        SUBSTRING(da.name, 0, CHARINDEX('-', da.name)) AS Source_Server ,
        a.article ,
        a.source_object ,
        s.subscriber_db ,
        REVERSE(SUBSTRING(REVERSE(da.name),
                          CHARINDEX('-', REVERSE(da.name)) + 1,
                          ( ( CHARINDEX('-', REVERSE(da.name),
                                        CHARINDEX('-', REVERSE(da.name)) + 1) )
                            - ( CHARINDEX('-', REVERSE(da.name)) ) - 1 ))) AS Destination_Server ,
        a.destination_object ,
        da.name AS DistributionAgentName ,
        sa.name AS SnapShotAgentName
FROM    distribution.dbo.MSpublications p
        JOIN distribution.dbo.MSarticles a ON a.publisher_id = p.publisher_id
                                              AND a.publication_id = p.publication_id
        JOIN distribution.dbo.MSsubscriptions s ON s.publisher_id = p.publisher_id
                                                   AND s.publication_id = p.publication_id
                                                   AND s.article_id = a.article_id
        JOIN distribution.dbo.MSdistribution_agents da ON da.publisher_id = p.publisher_id
                                                          AND da.publisher_db = p.publisher_db
                                                          AND da.subscriber_db = s.subscriber_db
        JOIN distribution.dbo.MSsnapshot_agents sa ON p.publication = sa.publication
ORDER BY p.publication ,
        p.publisher_db ,
        a.article ,
        a.source_object ,
        s.subscriber_db;

FacebookTwitterGoogle+Share