Change tracking help SQL
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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
This page was edited 47 days ago on 02/10/2024. What links here