SQL Server: subscription: how to find out if a table is under replication / subscription

Question

In SQL Server, in the Subscription section, how can you find out if a table is under replication / subscription?

Any idea?

Answer 1

I'm not sure there is a simple answer to this question, and I think the answers may vary depending on the type of replication. I think you may have to rely on heuristics to answer it.

For replicating snapshots, I can't think of anything that would give the game. Obviously, the presence of replication tables (for example, MSreplication_objects ) tells you that replication occurs in the database, but there are no specific ones as far as I know.

For transactional replication (without updating), you can go through MSreplication_objects (which will list some saved procs), and then use sys.sql_dependencies to find the tables that they relate to

To replicate a transaction (update), you can look at MSsubscription_articles (or look for the presence of update subscription table triggers)

You can look at sysmergearticles for merge replication, but you also need to look in sysmergesubscriptions to determine if you are on the subscription side.

Answer 2

Proceed to check the subscriber database for the dbo.MSreplication_subscriptions table. If the database is subscriber , you will find this table. Also, to find articles, use this in the subscribed database

 SELECT publisher,Publisher_Db,publication,article FROM dbo.MSreplication_objects 

Answer 3

The simplest way would be to create a linked server with the main server and query the [distribution] table. [dbo]. [MSarticles].

Answer 4

I used the idea of Davien Unbeliever (+1) to create this code that worked for me

 SELECT DISTINCT ot.object_id ,ot.schema_id ,r.publisher ,r.publisher_db ,r.publication ,r.article FROM dbo.MSreplication_objects R INNER JOIN sys.objects so ON r.object_name = so.name AND so.type = 'P' --stored procedures INNER JOIN sys.sql_dependencies dp ON so.object_id = dp.object_id INNER JOIN sys.objects ot ON dp.referenced_major_id = ot.object_id --objects AND r.article = ot.name 

Answer 5

Take a look at the DATABASEPROPERTYEX. It has the option "IsSubscribed", which should do what you want.