问题描述
如何获取在给定版本后具有任何跟踪更改的所有表(已启用更改跟踪)的列表?
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;