How to check if change tracking is enabled

Question

I'm trying to determine if Change Tracking is already enabled on my database before I run the ALTER DATABASE command to enable it. I am trying to prevent errors with these scripts if run multiple times.

I checked in sys.databases and sys.dm_tran_commit_table but was unable to find what I was looking for.

Fcc

Answer 1

You can use this query:

SELECT * 
FROM sys.change_tracking_databases 
WHERE database_id=DB_ID('MyDatabase')

Filum

So I was unfamiliar with Change Tracking and found this question searching about Change Data Capture (CDC). For others who happen to be here, the equivalent question/answer for CDC is found here SELECT s.name AS Schema_Name, tb.name AS Table_Name , tb.object_id, tb.type, tb.type_desc, tb.is_tracked_by_cdc FROM sys.tables tb INNER JOIN sys.schemas s on s.schema_id = tb.schema_id WHERE tb.is_tracked_by_cdc = 1 – Lands

To check the current database, you can use SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID() (no parameter)( – Taishataisho

to help others, if you need to enable / disable this configuration you can use: ALTER DATABASE MyDatabaseName SET CHANGE_TRACKING = ON – Impermissible