MDrivenWiki
Log in

Change tracking help SQL

From MDrivenWiki
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
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