Enable change tracking on all tables in database

Question

Assuming change tracking is enabled on a SQL Server database, how do I enable change tracking on all the tables in the database?

Answer 1

You could use following T-SQL script to generate another T-SQL script which enable CHANGE TRACKING feature on all tables with primary keys:

-- Step #1: Execute below script having [Results to text] option selected (Ctrl + T)
SET NOCOUNT ON;
GO    

-- Is CHANGE TRACKING enabled at database level ?
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

-- It generates the final T-SQL script
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
    JOIN    sys.schemas s ON t.schema_id = s.schema_id 
    WHERE   x.[type] = 'PK'
) y
ORDER BY schema_name, table_name

Results tab:

PRINT 'Enable CHANGE_TRACKING on [dbo].[Documents]';ALTER TABLE [dbo].[Documents] ENABLE CHANGE_TRACKING
GO
PRINT 'Enable CHANGE_TRACKING on [dbo].[Events]';ALTER TABLE [dbo].[Events] ENABLE CHANGE_TRACKING
GO
PRINT 'Enable CHANGE_TRACKING on [dbo].[Names]';ALTER TABLE [dbo].[Names] ENABLE CHANGE_TRACKING
GO

Step #2: select all text from [Results] tab, copy text, open a new query window (Ctrl + N), paste and execute this script.

Results tab:

Enable CHANGE_TRACKING on [dbo].[Documents]
Enable CHANGE_TRACKING on [dbo].[Events]
Enable CHANGE_TRACKING on [dbo].[Names]