Thursday, February 12, 2009

[Replication] List your subscriptions, including articles, from the subscriber machine

Sometimes you need to know what articles you've received via a subscription. From Computer B (which is a subscriber), you can run this against Computer a (the publisher or distributor) and get a list of articles (tables) that are being replicated. A rare thing to need, but durn handy. You will need a linked server on Computer A that can read from Computer B, and you need to run it against each replicated database.

(remember that are you need to copy even though you can't see the whole line
DECLARE @sqlstatement VARCHAR(1000)
SELECT @sqlstatement = 'SELECT identity(int, 1,1) as ID,
* INTO ##Publications FROM OPENQUERY([computera],
''SET FMTONLY OFF {call replicated_db..sp_helpsubscription}'')'

SELECT * FROM ##publications where subscriber = @@servername
DROP TABLE ##publications

No comments: