June 4, 2013 at 8:49 am
I need to programmatically to get a list of all replicated articles for a given database.
I tried select * from sys.dm_repl_articles, but it returned only 2 out of 8 articles.
In Replication properties/Articles GUI I see 8 checked tables as articles.
What another sys.* or sp_* can I use?
Thanks
June 4, 2013 at 8:24 pm
Query the distribution database:SELECT article FROM distribution.dbo.msarticles WHERE publication_id = X order by article_id
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 5, 2013 at 12:26 am
This script returns a complete set of replication information.
SELECT DISTINCT
srv.srvname publication_server
, a.publisher_db
, p.publication publication_name
, a.article
, a.destination_object
, ss.srvname subscription_server
, s.subscriber_db
, da.name AS distribution_agent_job_name
FROM MSArticles a
JOIN MSpublications p ON a.publication_id = p.publication_id
JOIN MSsubscriptions s ON p.publication_id = s.publication_id
JOIN master..sysservers ss ON s.subscriber_id = ss.srvid
JOIN master..sysservers srv ON srv.srvid = p.publisher_id
JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id
AND da.subscriber_id = s.subscriber_id
ORDER BY 1,2,3
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply