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]

Answer 2

The script below will enable change tracking on all database tables:

-- This script enables change tracking on all tables with primary keys.
declare @pkTables as table (name nvarchar(1000));
insert into @pkTables (name)
select distinct tc.TABLE_NAME as name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
where tc.CONSTRAINT_TYPE = 'Primary Key'

-- Keep track of how many tables we are dealing with
declare @enabled int
declare @alreadyEnabled int
set @enabled = 0
set @alreadyEnabled = 0
select count(*) as tablesWithPrimaryKeys from @pkTables

-- For each table name in primary key tables
declare @tableName nvarchar(1000)
while exists (select * from @pkTables)
begin
  -- Set the current table name
  select top 1 @tableName = name from @pkTables order by name asc

  -- Enable change tracking for this table
  if exists(
    select sys.schemas.name as schemaName, sys.tables.name as tableName from sys.change_tracking_tables
    join sys.tables on sys.tables.object_id = sys.change_tracking_tables.object_id
    join sys.schemas on sys.schemas.schema_id = sys.tables.schema_id
    where sys.tables.name = @tableName
  )
  begin
    set @alreadyEnabled = @alreadyEnabled + 1
  end
  else
  begin
    exec('alter table ' + @tableName + ' enable change_tracking')
    set @enabled = @enabled + 1
  end

  -- Delete the current table name
  delete @pkTables where name = @tableName

end

-- enabled + alreadyEnabled must equal tablesWithPrimaryKeys
select @enabled as tablesEnabled, @alreadyEnabled as tablesAlreadyEnabled