Microsoft SQL Server：如何检测两个独立数据库的表列差异？

Got it, let's tackle this table structure comparison problem between your two SQL Server databases. Here are a couple of reliable approaches to identify column-level differences, starting with a T-SQL script as you requested, followed by some solid open-source tools:

T-SQL脚本实现 #

First up, here's a T-SQL script that queries SQL Server's system catalog views directly to spot column differences. It runs right in SSMS, no external tools needed, and will catch both missing columns (like your Column_WTF in Table_A of MY_DB2) and attribute differences (like data type or length mismatches).

-- 对比MY_DB1和MY_DB2的表列差异
SELECT
    COALESCE(t1.name, t2.name) AS TableName,
    COALESCE(c1.name, c2.name) AS ColumnName,
    CASE
        WHEN c1.name IS NULL THEN '仅存在于MY_DB2'
        WHEN c2.name IS NULL THEN '仅存在于MY_DB1'
        ELSE '列属性存在差异'
    END AS DifferenceType,
    -- MY_DB1的列属性
    'MY_DB1' AS SourceDB1,
    COALESCE(col1.data_type, 'N/A') AS DB1_DataType,
    COALESCE(CAST(c1.max_length AS VARCHAR), 'N/A') AS DB1_MaxLength,
    COALESCE(CAST(c1.precision AS VARCHAR), 'N/A') AS DB1_Precision,
    COALESCE(CAST(c1.scale AS VARCHAR), 'N/A') AS DB1_Scale,
    -- MY_DB2的列属性
    'MY_DB2' AS SourceDB2,
    COALESCE(col2.data_type, 'N/A') AS DB2_DataType,
    COALESCE(CAST(c2.max_length AS VARCHAR), 'N/A') AS DB2_MaxLength,
    COALESCE(CAST(c2.precision AS VARCHAR), 'N/A') AS DB2_Precision,
    COALESCE(CAST(c2.scale AS VARCHAR), 'N/A') AS DB2_Scale
FROM
    MY_DB1.sys.tables t1
    FULL JOIN MY_DB2.sys.tables t2 ON t1.name = t2.name
    FULL JOIN MY_DB1.sys.columns c1 ON t1.object_id = c1.object_id
    FULL JOIN MY_DB2.sys.columns c2 ON t2.object_id = c2.object_id AND c1.name = c2.name
    -- 关联INFORMATION_SCHEMA获取易读的数据类型名称
    LEFT JOIN MY_DB1.INFORMATION_SCHEMA.COLUMNS col1 
        ON col1.TABLE_NAME = t1.name AND col1.COLUMN_NAME = c1.name
    LEFT JOIN MY_DB2.INFORMATION_SCHEMA.COLUMNS col2 
        ON col2.TABLE_NAME = t2.name AND col2.COLUMN_NAME = c2.name
WHERE
    -- 过滤掉完全匹配的列，只保留差异项
    (c1.name IS NULL OR c2.name IS NULL OR 
     col1.data_type <> col2.data_type OR 
     c1.max_length <> c2.max_length OR 
     c1.precision <> c2.precision OR 
     c1.scale <> c2.scale)
    -- 只对比用户自定义表，排除系统表
    AND (t1.type = 'U' OR t2.type = 'U')
ORDER BY
    TableName, ColumnName;

脚本说明 #

• Uses FULL JOIN to match tables and columns across both databases, so it won't miss tables that exist in only one DB.
• The CASE clause clearly labels the type of difference (missing column vs. attribute mismatch).
• Includes key column attributes like data type, max length, precision, and scale to catch subtle differences beyond just column presence.
• Filters out system tables to focus only on your user data tables.

开源工具推荐 #

If you need to compare more than just columns (like indexes, constraints, stored procedures) or want a visual interface, these open-source/free tools work great for SQL Server:

• Open DBDiff: A lightweight, open-source (MIT license) tool built specifically for SQL Server schema comparison. You can point it at both databases, run a comparison, and get a clear report of all differences—including missing columns. It can also generate sync scripts if you need to align the schemas.
• SchemaSpy: While primarily a database documentation tool, it can compare two schemas and generate HTML reports highlighting differences. It requires Java to run, but it's free and supports SQL Server well.
• SQL Server Data Tools (SSDT): Microsoft's free, official tool integrated with Visual Studio. Its Schema Compare feature lets you visually inspect differences between two databases, filter down to just tables/columns, and generate deployment scripts. It's not fully open-source, but it's widely used and completely free.

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