Wednesday 6 May 2009

How to find if and where your table is replicated

Another in the sporadic series of I need to do but I cant find it on the internet.

I have a table that I think might be involved in replication but I dont know which publication its in...

We know the table name - 'MyTable'
We have replication running on our server and its replicating our database, or part of it - 'MyDatabase'
we need to know if the table is replicated and if so which publication is going to need to be reviewed if we make changes to the table.

How?

USE MyDatabase
GO

/* Lots of info about our table but not much thats relevant to our current requirements*/
SELECT
*
FROM
sysobjects
WHERE
NAME = 'MyTable'

-- mmmm, getting there
/* To quote BOL - "Contains one row for each merge article defined in the local database. This table is stored in the publication database.replication"

interesting column is [pubid]
*/
SELECT
*
FROM
dbo.sysmergearticles AS s
WHERE
NAME = 'MyTable'

-- really close now
/*
the sysmergepublications table - Contains one row for each merge publication defined in the database. This table is stored in the publication and subscription databases.

so this would be where we get the publication details
*/
SELECT
*
FROM
dbo.sysmergepublications AS s
WHERE
s.pubid = '2876BBD8-3D4E-4ED8-88F3-581A659E8144'

-- DONE IT.
/*
Combine the two tables above and we get the information we need
*/
SELECT
s.[name] AS [Publication name]
FROM
dbo.sysmergepublications AS s
INNER JOIN dbo.sysmergearticles AS s2 ON s.pubid = s2.pubid
WHERE
s2.NAME = 'MyTable'

So I now know which

AddThis

Bookmark and Share
 
Google Analytics Alternative