CODEWITHSUNDEEP
sql-servert-sql
Karol Kolenda
Karol Kolenda

Reputation: 1690

How to get a list of all changed tables from SQL Server Change Tracking

How to get a list of all tables (which already have Change Tracking enabled) which have any tracked changes after given version?

Upvotes: 7

Views: 9196

Answers (2)

mozey
mozey

Reputation: 2292

This will return a list of all the tables that have changed since the previous tracking version:

set nocount on;

-- We want to check for changes since the previous version
--declare @prevTrackingVersion int = INSERT_YOUR_PREV_VERSION_HERE
-- Comment out this line if you know the previous version
declare @prevTrackingVersion int = CHANGE_TRACKING_CURRENT_VERSION() - 1

-- Get a list of table with change tracking enabled
declare @trackedTables as table (name nvarchar(1000));
insert into @trackedTables (name)
select sys.tables.name from sys.change_tracking_tables 
join sys.tables ON tables.object_id = change_tracking_tables.object_id

-- This will be the list of tables with changes
declare @changedTables as table (name nvarchar(1000));

-- For each table name in tracked tables
declare @tableName nvarchar(1000)
while exists(select top 1 * from @trackedTables)
begin
  -- Set the current table name
  set @tableName = (select top 1 name from @trackedTables order by name asc);

  -- Determine if the table has changed since the previous version
  declare @sql nvarchar(250)
  declare @retVal int 
  set @sql = 'select @retVal = count(*) from changetable(changes ' + @tableName + ', ' + cast(@prevTrackingVersion as varchar) + ') as changedTable'
  exec sp_executesql @sql, N'@retVal int output', @retVal output 
  if @retval > 0
  begin
    insert into @changedTables (name) select @tableName
  end

  -- Delete the current table name 
  delete from @trackedTables where name = @tableName;  
end

select * from @changedTables;

Upvotes: 5

Nick
Nick

Reputation: 112

Well to get a list of all tables that have change tracking enabled you would perform a query like

SELECT sys.tables.name FROM sys.change_tracking_tables 
JOIN sys.tables ON tables.object_id = change_tracking_tables.object_id

Then you can add a where condition for the version if you'd like to. I believe that answers your question.

Also if you'd like to see some info on the change you can run a query like the one below for a specific table using the changetable function.

DECLARE @synchronization_version NVARCHAR(MAX),@last_synchronization_version NVARCHAR(MAX)
SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();
SELECT
CT.*
FROM
CHANGETABLE(CHANGES Sales.CreditCard, @last_synchronization_version) AS CT

UPDATE

I updated the original query to perform a look and print the results, you'l be able to review the tables before you exec the query since you have over 1000 tables per your comment you might want to remove some.

SET NOCOUNT ON;
DECLARE @Views as TABLE (name nvarchar(200));

INSERT INTO @Views (name)
SELECT sys.tables.name FROM sys.change_tracking_tables 
JOIN sys.tables ON tables.object_id = change_tracking_tables.object_id


DECLARE @viewName   nvarchar(200) = (select top 1 name from @Views);
DECLARE @sql        nvarchar(max) = '';
DECLARE @union NVARCHAR(20)
DECLARE @sql1 NVARCHAR(max)

SET @sql1 = 'DECLARE @synchronization_version NVARCHAR(MAX),@last_synchronization_versionNVARCHAR(MAX)
SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();'
PRINT(@sql1)
WHILE(Exists(select 1 from @Views)) BEGIN
SET @union = '';
    SET @sql = '
SELECT
    CT.*
FROM
CHANGETABLE(CHANGES ' + @ViewName +', @last_synchronization_version) AS CT'
IF (SELECT COUNT(name) FROM @Views) > 2
BEGIN
SET @union = '  UNION'      
END 
Print (@sql+@union);
DELETE FROM @Views where name = @viewName;
SET @ViewName = (select top 1 name from @Views);
END;

Upvotes: 4

Related Questions