Change tracking help SQL
This page was created by Lars.olofsson on 2018-05-03. Last edited by Edgar on 2025-01-20.
This is the stored procedure for removing all change tracking on the current database:
CREATE PROCEDURE [dbo].[spTurnOffChangeTracking] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Turn off change tracking for the tables, one by one DECLARE @name VARCHAR(100) DECLARE @command VARCHAR(400) DECLARE db_cursor CURSOR FOR SELECT Table_name FROM ChangeTrackedTables OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN set @command = 'ALTER TABLE ' + @name + ' DISABLE CHANGE_TRACKING' exec(@command) FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor -- Turn off change tracking for the database ALTER DATABASE Kubik SET CHANGE_TRACKING = OFF END
The view for listing change tracked tables:
CREATE VIEW [dbo].[ChangeTrackedTables] AS SELECT TOP (100) PERCENT s.name AS Schema_name, t.name AS Table_name FROM sys.change_tracking_tables AS ctt INNER JOIN sys.tables AS t ON t.object_id = ctt.object_id INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id ORDER BY Schema_name, Table_name