Context

When replicating your SQL Server data with a data integration tool. You mainly have two options: Change Tracking (CT) or Change Data Capture (CDC). Different services use different approaches to replicate your data, Airbyte uses CDC, Stitch uses CT, Fivetran provides you with both for you to decide which one is best.

At this point, you have decided you are going to use CT, you are following the instructions to setup your source DB for replication, and you find out there are a gazillion tables you have to enable CT for with the following commands. One for each table 😱.

ALTER TABLE [<schema>].[<table>] ENABLE CHANGE_TRACKING;
GRANT VIEW CHANGE TRACKING ON [<schema>].[<table>] TO <username>;

Build your command

There is a very simple trick to help you out: printing all the tables in your database and giving the proper formatting together according to the commands above

select 
  'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ENABLE CHANGE_TRACKING;'
from
  INFORMATION_SCHEMA.tables
--where TABLE_NAME not like '%empl%';

If there are tables in your database you don’t want to enable CT on, you can use a WHERE clause to exclude or include any of them.

Execute

Once the command is executed you can just use ⌘/Ctrl + c / ⌘/Ctrl + v to copy paste the output into a script and run it as normal.

Footnotes

1. The example only shows the ALTER table command, you shall do this too for the GRANT VIEW CHANGE TRACKING ON ... command.