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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy