如何识别两数据库相似表并对比同名表列差异、修正SQL

问题背景 #

需要在存储了多张数据表的两个数据库中，找出同名表的列信息差异。

示例场景 #

• DB1中存在名为TEMP的表，列包含：PersonID、LastNamee、FirstName、Address、City
• DB2中存在同名TEMP表，列包含：ID、FirstName、LastName、Address、City

原有查询与异常表现 #

原查询语句 #

SELECT 
A.TABLE_CATALOG,
A.TABLE_NAME,
A.COLUMN_NAME,
B.TABLE_CATALOG,
B.TABLE_NAME,
B.COLUMN_NAME,
'COLUMN Not Match' AS RESULT
FROM DB1.INFORMATION_SCHEMA.COLUMNS A FULL OUTER JOIN DB2.INFORMATION_SCHEMA.COLUMNS B 
ON A.TABLE_NAME = B.TABLE_NAME AND B.COLUMN_NAME = A.COLUMN_NAME
INNER JOIN DB2.INFORMATION_SCHEMA.COLUMNS BB 
ON A.TABLE_NAME = BB.TABLE_NAME
WHERE B.TABLE_NAME IS NULL OR B.TABLE_NAME IS NULL AND A.TABLE_NAME= BB.TABLE_NAME

异常返回结果 #

运行后出现大量重复数据，示例如下：

TABLE_CATALOG   | TABLE_NAME | COLUMN_NAME | TABLE_CATALOG | TABLE_NAME  | COLUMN_NAME 
DB1                 temp         PersonID        NULL           NULL          NULL
DB1                 temp         PersonID        NULL           NULL          NULL
DB1                 temp         PersonID        NULL           NULL          NULL
DB1                 temp         PersonID        NULL           NULL          NULL
DB1                 temp         PersonID        NULL           NULL          NULL
DB1                 temp         LastNamee       NULL           NULL          NULL
DB1                 temp         LastNamee       NULL           NULL          NULL
DB1                 temp         LastNamee       NULL           NULL          NULL
DB1                 temp         LastNamee       NULL           NULL          NULL
DB1                 temp         LastNamee       NULL           NULL          NULL

期望返回结果 #

仅针对两库共有的同名表，返回两边不匹配的列，格式如下：

TABLE_CATALOG   | TABLE_NAME | COLUMN_NAME | TABLE_CATALOG | TABLE_NAME  | COLUMN_NAME 
DB1                 temp         PersonID        DB2            temp          ID
DB1                 temp         LastNamee       DB2            temp          LastName

解决方案 #

原有语句错误点 #

• 冗余关联BB表未做去重处理，触发笛卡尔积，是结果出现大量重复的核心原因
• WHERE条件逻辑错误，重复书写B.TABLE_NAME IS NULL判断，未正确限定仅对比两库共有的同名表
• 仅通过全连接匹配同名列，未对两边独有的列做对齐处理，无法返回期望的左右列对应结果

修正后可直接使用的查询语句 #

WITH 
-- 筛选两个库共有的同名表
common_tables AS (
    SELECT DISTINCT A.TABLE_NAME
    FROM DB1.INFORMATION_SCHEMA.COLUMNS A
    INNER JOIN DB2.INFORMATION_SCHEMA.COLUMNS B
        ON A.TABLE_NAME = B.TABLE_NAME
),
-- 提取DB1共有表的列信息
db1_cols AS (
    SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME
    FROM DB1.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM common_tables)
),
-- 提取DB2共有表的列信息
db2_cols AS (
    SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME
    FROM DB2.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM common_tables)
),
-- 统计DB1存在、DB2不存在的列
db1_unique_cols AS (
    SELECT 
        d1.TABLE_CATALOG AS db1_catalog,
        d1.TABLE_NAME,
        d1.COLUMN_NAME AS db1_col,
        ROW_NUMBER() OVER (PARTITION BY d1.TABLE_NAME ORDER BY d1.COLUMN_NAME) AS col_rn
    FROM db1_cols d1
    LEFT JOIN db2_cols d2
        ON d1.TABLE_NAME = d2.TABLE_NAME AND d1.COLUMN_NAME = d2.COLUMN_NAME
    WHERE d2.COLUMN_NAME IS NULL
),
-- 统计DB2存在、DB1不存在的列
db2_unique_cols AS (
    SELECT 
        d2.TABLE_CATALOG AS db2_catalog,
        d2.TABLE_NAME,
        d2.COLUMN_NAME AS db2_col,
        ROW_NUMBER() OVER (PARTITION BY d2.TABLE_NAME ORDER BY d2.COLUMN_NAME) AS col_rn
    FROM db2_cols d2
    LEFT JOIN db1_cols d1
        ON d1.TABLE_NAME = d2.TABLE_NAME AND d1.COLUMN_NAME = d2.COLUMN_NAME
    WHERE d1.COLUMN_NAME IS NULL
)
-- 对齐两边差异列输出结果
SELECT 
    dm1.db1_catalog AS TABLE_CATALOG,
    COALESCE(dm1.TABLE_NAME, dm2.TABLE_NAME) AS TABLE_NAME,
    dm1.db1_col AS COLUMN_NAME,
    dm2.db2_catalog AS TABLE_CATALOG,
    COALESCE(dm1.TABLE_NAME, dm2.TABLE_NAME) AS TABLE_NAME,
    dm2.db2_col AS COLUMN_NAME,
    'COLUMN Not Match' AS RESULT
FROM db1_unique_cols dm1
FULL OUTER JOIN db2_unique_cols dm2
    ON dm1.TABLE_NAME = dm2.TABLE_NAME AND dm1.col_rn = dm2.col_rn
ORDER BY TABLE_NAME, dm1.col_rn, dm2.col_rn

语句说明 #

• 自动过滤单库独有的表，仅对比两个库同时存在的同名表
• 无重复数据，支持批量对比库下所有同名表的列差异
• 当两边独有列数量不一致时，数量少的一侧对应字段返回NULL，符合差异校验逻辑

内容的提问来源于stack exchange，提问作者Deepak Ananth