sql - 如何有效地比较两个数据集的差异？

问题描述

我创建了几个查询，它们提供了一些关于我们数据库结构的数据点，一个用于表，另一个用于视图：

表：

SELECT t.TABLE_SCHEMA
    , t.TABLE_NAME
    , c.COLUMN_NAME
    , c.ORDINAL_POSITION
    , c.COLUMN_DEFAULT
    , c.IS_NULLABLE
    , c.DATA_TYPE
    , c.CHARACTER_MAXIMUM_LENGTH
    , c.NUMERIC_PRECISION
    , c.NUMERIC_PRECISION_RADIX
    , c.NUMERIC_SCALE
    , ccu.CONSTRAINT_NAME
    , tc.CONSTRAINT_TYPE
    , rc.UNIQUE_CONSTRAINT_NAME
    , rc.MATCH_OPTION
    , rc.DELETE_RULE
    , rc.UPDATE_RULE
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON (c.TABLE_NAME = ccu.TABLE_NAME AND c.COLUMN_NAME = ccu.COLUMN_NAME)
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'

意见：

SELECT t.TABLE_SCHEMA AS 'VIEW_SCHEMA'
    , t.TABLE_NAME AS 'VIEW_NAME'
    , c.COLUMN_NAME
    , c.ORDINAL_POSITION
    , c.COLUMN_DEFAULT
    , c.IS_NULLABLE
    , c.DATA_TYPE
    , c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'VIEW'

我的意图是我可以针对我们的生产环境运行查询并将输出保存为“快照”。然后，当我们在开发环境中进行更改时，我可以在那里运行查询，并获取另一个“快照”。通过当前与更改的快照，我可以将它们相互比较以分析差异。

注意：是的，我知道像 RedGate 之类的东西有这样的工具，但第三方工具对我来说不是一个选择......其次，最终目标是有一些我可以变成报告的东西可以通过电子邮件或 PDF 或其他方式发送。

我选择将表/列对上的所有内容锚定为“主键”，因为这似乎是判断是否存在新/删除/更改列的最佳方法，并且我的查询结果保存到两个表中：[Meta].[SchemaChanges_New]和[Meta].[SchemaChanges_Current]。我不包括表的架构，因为我想知道这是否是一个更改，而不是在 JOIN 中使用它，然后它将导致更改识别为新项目与已删除项目。

所以识别新项目的查询非常简单：

SELECT n.*
FROM [Meta].[SchemaChanges_New] n
LEFT JOIN [Meta].[SchemaChanges_Current] c 
  ON (n.[TABLE_NAME] = c.[TABLE_NAME] AND n.[COLUMN_NAME] = c.[COLUMN_NAME])
WHERE (c.[TABLE_NAME] IS NULL OR c.[COLUMN_NAME] IS NULL)
ORDER BY n.[TABLE_NAME], n.[ORDINAL_POSITION]

...以及用于识别已删除项目的查询同样如此：

SELECT c.*
FROM [Meta].[SchemaChanges_Current] c
LEFT JOIN [Meta].[SchemaChanges_New] n 
  ON (c.[TABLE_NAME] = n.[TABLE_NAME] AND c.[COLUMN_NAME] = n.[COLUMN_NAME])
WHERE (n.[TABLE_NAME] IS NULL OR n.[COLUMN_NAME] IS NULL)
ORDER BY n.[TABLE_NAME], n.[ORDINAL_POSITION]

*如果有更合适的方法来实现这一点，我也会全力以赴。

我的困境是试图找出最好/最有效的方法来识别那些已更改的项目。而且因为我也在捕获索引信息，所以给定的表/列可能会发生多次更改。

第一次尝试老式的“蛮力”方法似乎工作得相当不错：

SELECT c.*, n.*
FROM [Meta].[SchemaChanges_New] n
INNER JOIN [Meta].[SchemaChanges_Current] c 
   ON (n.[TABLE_NAME] = c.[TABLE_NAME] AND n.[COLUMN_NAME] = c.[COLUMN_NAME])
WHERE c.TABLE_SCHEMA != n.TABLE_SCHEMA
   OR c.ORDINAL_POSITION != n.ORDINAL_POSITION
   OR IsNull(c.COLUMN_DEFAULT, '') != IsNull(n.COLUMN_DEFAULT, '')
   OR IsNull(c.IS_NULLABLE, '') != IsNull(n.IS_NULLABLE, '')
   OR IsNull(c.DATA_TYPE, '') != IsNull(n.DATA_TYPE, '')
   OR IsNull(c.CHARACTER_MAXIMUM_LENGTH, '') != IsNull(n.CHARACTER_MAXIMUM_LENGTH, '')
   OR IsNull(c.NUMERIC_PRECISION, '') != IsNull(n.NUMERIC_PRECISION, '')
   OR IsNull(c.NUMERIC_PRECISION_RADIX, '') != IsNull(n.NUMERIC_PRECISION_RADIX, '')
   OR IsNull(c.NUMERIC_SCALE, '') != IsNull(n.NUMERIC_SCALE, '')
   OR IsNull(c.CONSTRAINT_NAME, '') != IsNull(n.CONSTRAINT_NAME, '')
   OR IsNull(c.CONSTRAINT_TYPE, '') != IsNull(n.CONSTRAINT_TYPE, '')
   OR IsNull(c.UNIQUE_CONSTRAINT_NAME, '') != IsNull(n.UNIQUE_CONSTRAINT_NAME, '')
   OR IsNull(c.MATCH_OPTION, '') != IsNull(n.MATCH_OPTION, '')
   OR IsNull(c.DELETE_RULE, '') != IsNull(n.DELETE_RULE, '')
   OR IsNull(c.UPDATE_RULE, '') != IsNull(n.UPDATE_RULE, '')

但是，我发现当某些列具有多个关联的索引时，由于“简单”的 JOIN 可能会出现误报。但是，如果我向 JOIN 添加更多连接，我可能会冒更改的项目从结果集中脱落的风险。

因此，我正在寻求有关如何解决此问题的帮助...以便我可以识别表/视图或相关索引的结构，构成，组成等的任何变化，无论多么微小，但也消除由于两个快照之间的记录不匹配而导致的误报。

谢谢您的帮助！

标签： sqlsql-serversql-server-2012

sql - 如何有效地比较两个数据集的差异？

问题描述

解决方案

推荐阅读