如何比较DB1与DB2的数据模型差异？求推荐sproc或免费工具

Great question! Comparing database schemas between two databases is a super common task, and there are both DIY stored procedure options and solid free tools to help you out. Let me break this down for you:

If you prefer a custom, database-native solution, you can build stored procedures using system catalog views to identify differences. Here’s how to approach it for common database systems:

SQL Server Example Sproc #

This procedure compares tables and columns between two databases, flagging missing/extra objects and data type mismatches:

CREATE PROCEDURE sp_compare_schemas
    @db1 NVARCHAR(128),
    @db2 NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- Check for missing/extra tables
    PRINT '=== Missing/Extra Tables ===';
    EXEC (
        'SELECT ''' + @db1 + ''' AS SourceDB, name AS TableName, ''Missing in ' + @db2 + ''' AS Status
         FROM ' + QUOTENAME(@db1) + '.sys.tables
         WHERE name NOT IN (SELECT name FROM ' + QUOTENAME(@db2) + '.sys.tables)
         UNION ALL
         SELECT ''' + @db2 + ''' AS SourceDB, name AS TableName, ''Missing in ' + @db1 + ''' AS Status
         FROM ' + QUOTENAME(@db2) + '.sys.tables
         WHERE name NOT IN (SELECT name FROM ' + QUOTENAME(@db1) + '.sys.tables)'
    );

    -- Check column differences (missing columns, data type mismatches)
    PRINT CHAR(13) + '=== Column Differences ===';
    EXEC (
        'SELECT t.name AS TableName, 
                c1.name AS ColumnName, 
                CASE WHEN c2.name IS NULL THEN ''Missing in ' + @db2 + ''' 
                     WHEN c1.system_type_id != c2.system_type_id THEN ''Data type mismatch'' 
                     ELSE NULL END AS Status
         FROM ' + QUOTENAME(@db1) + '.sys.tables t
         JOIN ' + QUOTENAME(@db1) + '.sys.columns c1 ON t.object_id = c1.object_id
         LEFT JOIN ' + QUOTENAME(@db2) + '.sys.columns c2 ON t.object_id = c2.object_id AND c1.name = c2.name
         WHERE c2.name IS NULL OR c1.system_type_id != c2.system_type_id
         UNION ALL
         SELECT t.name AS TableName, 
                c2.name AS ColumnName, 
                ''Missing in ' + @db1 + ''' AS Status
         FROM ' + QUOTENAME(@db2) + '.sys.tables t
         JOIN ' + QUOTENAME(@db2) + '.sys.columns c2 ON t.object_id = c2.object_id
         LEFT JOIN ' + QUOTENAME(@db1) + '.sys.columns c1 ON t.object_id = c1.object_id AND c2.name = c1.name
         WHERE c1.name IS NULL'
    );
END

You can extend this to check indexes, constraints, or stored procedures by querying additional system views like sys.indexes, sys.key_constraints, or sys.procedures.

MySQL Example #

For MySQL, use the information_schema database to build a similar comparison logic. For example, comparing tables across two databases:

DELIMITER //
CREATE PROCEDURE compare_mysql_schemas(IN db1 VARCHAR(64), IN db2 VARCHAR(64))
BEGIN
    -- Missing/extra tables
    SELECT db1 AS SourceDB, table_name AS TableName, CONCAT('Missing in ', db2) AS Status
    FROM information_schema.tables
    WHERE table_schema = db1
    AND table_name NOT IN (SELECT table_name FROM information_schema.tables WHERE table_schema = db2)
    UNION ALL
    SELECT db2 AS SourceDB, table_name AS TableName, CONCAT('Missing in ', db1) AS Status
    FROM information_schema.tables
    WHERE table_schema = db2
    AND table_name NOT IN (SELECT table_name FROM information_schema.tables WHERE table_schema = db1);
END //
DELIMITER ;

If you don’t want to maintain custom code, these free tools handle most schema comparison needs out of the box:

• SQL Server Data Tools (SSDT)：Microsoft’s official tool integrated with Visual Studio. It compares schemas across databases, identifies differences in tables, columns, constraints, stored procedures, and generates synchronization scripts.
• SchemaSpy：Cross-database tool supporting SQL Server, MySQL, PostgreSQL, and more. It generates detailed HTML reports showing schema structures and differences, and requires a Java runtime.
• ApexSQL Compare Free Edition：A lightweight tool for SQL Server that detects schema differences and generates readable reports. The free version covers core comparison needs.
• MySQL Workbench：Built-in Schema Comparison tool for MySQL. It lets you compare two databases, visualize differences, and generate scripts to sync schemas.
• pgAdmin：For PostgreSQL users, the built-in Schema Diff tool (under the Tools menu) compares databases or schemas and highlights missing objects, naming differences, and structural changes.

A quick note: Custom stored procedures are great for tailored, lightweight checks, but tools are better for complex schemas with many object types (triggers, views, functions) since they handle edge cases automatically.

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