Posted by: Fatherjack | 06/May/09

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

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Categories

%d bloggers like this: