Which tables are using Change tracking

Identifying Tables with Change Tracking Enabled in SQL Server

Learn how to query SQL Server system views to determine which tables have Change Tracking enabled, a crucial feature for auditing and data synchronization.

Change Tracking in SQL Server is a lightweight solution that enables applications to easily determine what changes were made to user tables. It records information about rows that have been changed, but not the actual data that was changed. This feature is particularly useful for scenarios like incremental data synchronization or auditing. Understanding which tables have Change Tracking enabled is fundamental for database administrators and developers managing such systems.

Verifying Change Tracking at the Database Level

Before checking individual tables, it's important to confirm that Change Tracking is enabled for the database itself. If it's not enabled at the database level, no tables within that database can have Change Tracking active. You can query the sys.change_tracking_databases system view to check this status.

SELECT
    name AS DatabaseName,
    is_cdc_enabled AS IsChangeDataCaptureEnabled,
    is_broker_enabled AS IsServiceBrokerEnabled,
    is_change_tracking_on AS IsChangeTrackingOn
FROM sys.databases
WHERE is_change_tracking_on = 1;

Query to check if Change Tracking is enabled for databases

Identifying Tables with Change Tracking Enabled

Once you've confirmed Change Tracking is active for a database, you can then identify which specific tables within that database have it enabled. This information is stored in the sys.change_tracking_tables system view. Joining this view with sys.tables provides a comprehensive list of table names and their schemas.

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    ct.is_track_columns_updated_on AS IsTrackColumnsUpdatedOn
FROM sys.tables AS t
INNER JOIN sys.change_tracking_tables AS ct
    ON t.object_id = ct.object_id
WHERE t.is_ms_shipped = 0; -- Exclude system tables

Query to list all user tables with Change Tracking enabled

flowchart TD
    A[Start] --> B{Is Change Tracking enabled for Database?}
    B -- No --> C[End: No tables have CT]
    B -- Yes --> D[Query sys.change_tracking_tables]
    D --> E[Join with sys.tables]
    E --> F[Filter out system tables]
    F --> G[Display Schema and Table Names]
    G --> H[End]

Workflow for identifying tables with Change Tracking

Understanding is_track_columns_updated_on

The is_track_columns_updated_on column in sys.change_tracking_tables indicates whether column-level tracking is enabled for a table. When this is 1, Change Tracking not only records that a row changed but also which specific columns within that row were updated. This provides a more granular level of detail for applications that need to react to changes in particular columns.