如何从 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?

推荐答案

这将返回自上一个跟踪版本以来已更改的所有表的列表:

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;