Search code examples
sqlsql-serverreplication

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

In SQL Server, on the "Subscription side", how can you know if a table is under replication/subscription?

Any idea?

Solution

  • 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.