时间戳

首页 > 解决方案 > 如何查找受 SQL 影响的新记录集的表列表？

sql-server - 如何查找受 SQL 影响的新记录集的表列表？

问题描述

我正在将数据从一个环境导出到另一个环境。我想选择插入或修改了新记录集的表列表。

数据库有大约 200 个表，并且只有自昨天以来有 10 个表记录受到影响，我只想过滤这些表。其中一些表没有createate表列。基于对表的普通选择查询很难识别记录差异。

如何查找受 SQL 影响的新记录集的表列表？如果可能的话，只有那些来自已识别表的新受影响的记录。

我试过这个查询，但是这个查询没有返回实际的表。

select * from sysobjects where id in (
select object_id
FROM sys.dm_db_index_usage_stats
WHERE last_user_update > getdate() - 1 )

标签： sql-servertsql

解决方案

如果您没有时间戳或其他东西来识别新更改的记录，例如审计、利用触发器或在这些表上启用更改数据捕获，那么这是不可能做到的。

但是，阅读您的场景是不可能忽略已更改或修改的内容，而只是简单地将这 200 个表从一个环境导出到另一个环境并在目标位置覆盖它？

如果不是，那么您可能只对比较数据感兴趣，而不是识别新更改的记录来识别哪些表不匹配。你可以使用EXCEPT来做到这一点

请参阅下面的示例，该示例比较具有相同表名和模式的两个数据库，使用 EXCEPT 从两个数据库动态创建动态 SQL 语句列并在 while 循环中运行它们；将受影响的每个表名插入到临时表中。

DECLARE @Counter AS INT
    ,   @Query AS NVARCHAR(MAX)

IF OBJECT_ID('tempdb..#CompareRecords') IS NOT NULL DROP TABLE #CompareRecords
IF OBJECT_ID('tempdb..#TablesNotMatched') IS NOT NULL DROP TABLE #TablesNotMatched
CREATE TABLE #TablesNotMatched (ObjectName NVARCHAR(200))

SELECT
        ROW_NUMBER() OVER( ORDER BY (SELECT 1)) AS RowNr
    ,   t.TABLE_CATALOG
    ,   t.TABLE_SCHEMA
    ,   t.TABLE_NAME
    ,   Query =     'IF' +  CHAR(13)
                +   '(' + CHAR(13)
                +   '   SELECT' + CHAR(13)
                +   '       COUNT(*) + 1' + CHAR(13)
                +   '   FROM' + CHAR(13)
                +   '   (' + CHAR(13)
                +   '       SELECT ' + QUOTENAME(t.TABLE_NAME, '''''') + ' AS TableName, * FROM ' + QUOTENAME(t.TABLE_CATALOG) + '.' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + CHAR(13)
                +   '       EXCEPT' + CHAR(13)
                +   '       SELECT ' + QUOTENAME(t.TABLE_NAME, '''''') + ' AS TableName, * FROM ' + QUOTENAME(t2.TABLE_CATALOG) + '.' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + CHAR(13)
                +   '   ) AS sq' + CHAR(13)
                +   ') > 1' + CHAR(13)
                +   'SELECT ' + QUOTENAME(QUOTENAME(t.TABLE_CATALOG) + '.' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME), '''''') + ' AS TableNameRecordsNotMatched'
    INTO #CompareRecords
FROM <UAT_DATABASE>.INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN <PROD_DATABASE>.INFORMATION_SCHEMA.TABLES AS t2 ON    t.TABLE_SCHEMA = t2.TABLE_SCHEMA 
                                                            AND t.TABLE_NAME = t2.TABLE_NAME

WHERE t.TABLE_TYPE = 'BASE TABLE'


SET @Counter = (SELECT MAX(RowNr) FROM #CompareRecords)

WHILE @Counter > 0
    BEGIN
        SET @Query = (SELECT cr.Query FROM #CompareRecords AS cr WHERE cr.RowNr = @Counter)
        INSERT INTO #TablesNotMatched
        EXECUTE sp_executesql @Query
        SET @Counter = @Counter - 1
    END

SELECT
        *
FROM #TablesNotMatched

请注意，使用 EXCEPT 时，两个表必须具有完全相同的列数和类型。

我希望这会有所帮助。

推荐阅读