How Can I Check Whether I Have Enabled Change Tracking on Databases and Tables?

Question

How can I check whether I have enabled change tracking (CT) on individual databases and tables?

Environment

• Generic SQL Server
• Amazon RDS for SQL Server
• Azure SQL Database
• Azure SQL Managed Instance
• Google Cloud SQL for SQL Server

Answer

You can check whether you have enabled CT by querying the sys.change_tracking_databases and sys.change_tracking_tables SQL Server catalog views.

To check whether you have enabled CT on a database, use the following query:

SELECT DB_NAME(database_id) [DATABASE_NAME], * FROM sys.change_tracking_databases;

To check whether you have enabled CT on a table, use the following query:

SELECT OBJECT_NAME(object_id) [TABLE_NAME], * FROM sys.change_tracking_tables;