Find all tables with Change Tracking on your SQL Server

Change Tracking is a great feature on SQL Server, but it can have some performance implications. It is however one of those features that doesn’t jump out as obvious if it is enabled until you start digging around on the table properties dialog.

Here is where you can find it for a specific table.

But what if I don’t have time to right click on every table, and check the properties.

Here is a query that will show the Change Tracking enabled tables for a specific database:

SELECT '[' + s.name + '].[' + t.name + ']' AS Table_name
  FROM sys.change_tracking_tables tr
INNER JOIN sys.tables t on t.object_id = tr.object_id
INNER JOIN sys.schemas s on s.schema_id = t.schema_id
And here is how you would do it for all databases on your system.
CREATE TABLE ##ctTables
(
	TableName Varchar(100)
);

EXEC sp_MSforeachdb 'USE [?];
INSERT INTO ##ctTables
 SELECT ''[?].['' + s.name + ''].['' + t.name + '']'' AS Table_name
  FROM sys.change_tracking_tables tr
INNER JOIN sys.tables t on t.object_id = tr.object_id
INNER JOIN sys.schemas s on s.schema_id = t.schema_id
' ;

select * FROM ##ctTables;
drop table ##ctTables;
You might be surprised at how many tables have Change Tracking enabled.

More from Stedman Solutions:

 

Steve and the team at Stedman Solutions are here for all your SQL Server needs.

Contact us today for your  free 30 minute consultation..

We are ready to help!