How to check if change tracking is enabled on a specific table

Admin 1
Viewed 12676

For some reason our build system gives me an error saying change tracking is ALREADY enabled on a table I want to enable it on.

ALTER TABLE [dbo].[MyTable]
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = OFF)

I did a search but could't find something like an IF EXISTS to say first see if it is enabled or not, if it is not enabled then run that ALTER command.

sql-server sql
2 Answers
Admin1

Step #1: Execute below script having [Results to text] option selected (Ctrl + T)

    IF CONVERT(INT, PARSENAME(CONVERT(NVARCHAR(128), SERVERPROPERTY('ProductVersion')), 4)) >= 10 -- 10 = SQL2008
BEGIN
    EXEC sp_executesql N'SELECT * FROM sys.change_tracking_databases db WHERE db.database_id = DB_ID(); ' -- sys.change_tracking_databases is available in SQL2008+
    IF @@ROWCOUNT = 0
    BEGIN 
        RAISERROR('CHANGE TRACKING is not enabled at database level.', 16, 1);
        RETURN;
    END
END

And then query below will print all tables where tehre is no change tracking enabled

SELECT    --N'PRINT ''Enable CHANGE_TRACKING on ' + full_table_name + ''';'
         N'ALTER TABLE ' + full_table_name + N' ENABLE CHANGE_TRACKING' + CHAR(13) + CHAR(10) 
        --+ N'GO'
FROM (
    SELECT  QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS full_table_name,
            s.name AS schema_name, t.name AS table_name
    FROM    sys.key_constraints x
    JOIN    sys.tables t ON x.parent_object_id = t.object_id
    left JOIN   sys.change_tracking_tables
            on t.object_id = sys.change_tracking_tables.object_id
    JOIN    sys.schemas s ON t.schema_id = s.schema_id 
    WHERE   x.[type] = 'PK' and sys.change_tracking_tables.object_id is null
) y
ORDER BY schema_name, table_name
    enter code here
user_21329880
Related