Change tracking help SQL
Denis Pupin (talk | contribs) No edit summary |
No edit summary |
||
Line 1: | Line 1: | ||
Stored procedure for removing all change tracking on the current database{{Pre2|<nowiki> | Stored procedure for removing all change tracking on the current database:{{Pre2|<nowiki> | ||
CREATE PROCEDURE [dbo].[spTurnOffChangeTracking] | CREATE PROCEDURE [dbo].[spTurnOffChangeTracking] | ||
AS | AS | ||
Line 28: | Line 28: | ||
SET CHANGE_TRACKING = OFF | SET CHANGE_TRACKING = OFF | ||
END | END | ||
</nowiki>}}View for listing change tracked tables{{Pre2|<nowiki> | </nowiki>}}View for listing change tracked tables:{{Pre2|<nowiki> | ||
CREATE VIEW [dbo].[ChangeTrackedTables] | CREATE VIEW [dbo].[ChangeTrackedTables] | ||
AS | AS |
Revision as of 06:57, 20 February 2023
This page was created by Lars.olofsson on 2018-05-03. Last edited by Edgar on 2025-01-20.
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