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

In SQL Serve开发者_StackOverflow中文版r, on the "Subscription side", how can you know if a table is under replication/subscription?

Any idea?

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

For snapshot replication, I'm unable to think of anything that would give the game away. Obviously, the presence of the replication tables (e.g. MSreplication_objects) tells you that replication is occurring within the database, but there aren't any specific clues about tables, so far as I'm aware.

For transactional replication (non updating), you may be able to go via MSreplication_objects (which will list some stored procs) and then use sys.sql_dependencies to locate the tables that these relate to

For transaction replication (updating), you can look in MSsubscription_articles (or look for the presence of the subscription updating triggers against the table)

For merge replication, you can look in sysmergearticles, but you'd also have to look in sysmergesubscriptions to determine that you're on the subscription side.

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

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

I used Damien the Unbeliever's idea (+1) to produce 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

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

select * from [distribution].[dbo].[MSarticles]

Take a look at DATABASEPROPERTYEX. It has an 'IsSubscribed' option that should do what you want it to do.