Questa query confronta la struttura delle tabelle elencate in `wanted_tables` e visualizza solo le differenze.

Sostituire ogni occorrenza di `database_1` con il database a sinistra e di `database_2` con il database a destra.

WITH
wanted_tables AS (
    SELECT 'tabella1' AS table_name
    UNION ALL SELECT 'tabella2'
    UNION ALL SELECT 'tabella3'
    UNION ALL SELECT 'tabella4'
),
 
/* ============================================================
   TABELLE
   ============================================================ */
tables_cmp AS (
    SELECT
        wt.table_name,
        t1.TABLE_NAME IS NOT NULL AS exists_database_1,
        t2.TABLE_NAME IS NOT NULL AS exists_database_2,
 
        CONCAT_WS('|',
            t1.ENGINE,
            t1.ROW_FORMAT,
            t1.TABLE_COLLATION,
            t1.TABLE_TYPE
        ) AS definition_database_1,
 
        CONCAT_WS('|',
            t2.ENGINE,
            t2.ROW_FORMAT,
            t2.TABLE_COLLATION,
            t2.TABLE_TYPE
        ) AS definition_database_2
    FROM wanted_tables wt
    LEFT JOIN information_schema.TABLES t1
        ON t1.TABLE_SCHEMA = 'database_1'
       AND t1.TABLE_NAME = wt.table_name
    LEFT JOIN information_schema.TABLES t2
        ON t2.TABLE_SCHEMA = 'database_2'
       AND t2.TABLE_NAME = wt.table_name
),
 
/* ============================================================
   COLONNE
   ============================================================ */
columns_cmp AS (
    SELECT
        wt.table_name,
        COALESCE(c1.ORDINAL_POSITION, c2.ORDINAL_POSITION) AS pos,
 
        CONCAT(
            COALESCE(c1.COLUMN_NAME, '<MISSING>'), '|',
            COALESCE(c1.COLUMN_TYPE, '<NULL>'), '|',
            COALESCE(c1.IS_NULLABLE, '<NULL>'), '|',
            COALESCE(CAST(c1.COLUMN_DEFAULT AS CHAR), '<NULL>'), '|',
            COALESCE(c1.COLUMN_KEY, '<NULL>'), '|',
            COALESCE(c1.EXTRA, '<NULL>'), '|',
            COALESCE(c1.CHARACTER_SET_NAME, '<NULL>'), '|',
            COALESCE(c1.COLLATION_NAME, '<NULL>'), '|',
            COALESCE(c1.GENERATION_EXPRESSION, '<NULL>')
        ) AS definition_database_1,
 
        CONCAT(
            COALESCE(c2.COLUMN_NAME, '<MISSING>'), '|',
            COALESCE(c2.COLUMN_TYPE, '<NULL>'), '|',
            COALESCE(c2.IS_NULLABLE, '<NULL>'), '|',
            COALESCE(CAST(c2.COLUMN_DEFAULT AS CHAR), '<NULL>'), '|',
            COALESCE(c2.COLUMN_KEY, '<NULL>'), '|',
            COALESCE(c2.EXTRA, '<NULL>'), '|',
            COALESCE(c2.CHARACTER_SET_NAME, '<NULL>'), '|',
            COALESCE(c2.COLLATION_NAME, '<NULL>'), '|',
            COALESCE(c2.GENERATION_EXPRESSION, '<NULL>')
        ) AS definition_database_2,
 
        c1.COLUMN_NAME AS column_database_1,
        c2.COLUMN_NAME AS column_database_2
    FROM wanted_tables wt
    LEFT JOIN information_schema.COLUMNS c1
        ON c1.TABLE_SCHEMA = 'database_1'
       AND c1.TABLE_NAME = wt.table_name
    LEFT JOIN information_schema.COLUMNS c2
        ON c2.TABLE_SCHEMA = 'database_2'
       AND c2.TABLE_NAME = wt.table_name
       AND c2.ORDINAL_POSITION = c1.ORDINAL_POSITION
 
    UNION ALL
 
    SELECT
        wt.table_name,
        c2.ORDINAL_POSITION AS pos,
 
        '<MISSING>' AS definition_database_1,
 
        CONCAT(
            COALESCE(c2.COLUMN_NAME, '<MISSING>'), '|',
            COALESCE(c2.COLUMN_TYPE, '<NULL>'), '|',
            COALESCE(c2.IS_NULLABLE, '<NULL>'), '|',
            COALESCE(CAST(c2.COLUMN_DEFAULT AS CHAR), '<NULL>'), '|',
            COALESCE(c2.COLUMN_KEY, '<NULL>'), '|',
            COALESCE(c2.EXTRA, '<NULL>'), '|',
            COALESCE(c2.CHARACTER_SET_NAME, '<NULL>'), '|',
            COALESCE(c2.COLLATION_NAME, '<NULL>'), '|',
            COALESCE(c2.GENERATION_EXPRESSION, '<NULL>')
        ) AS definition_database_2,
 
        NULL AS column_database_1,
        c2.COLUMN_NAME AS column_database_2
    FROM wanted_tables wt
    JOIN information_schema.COLUMNS c2
        ON c2.TABLE_SCHEMA = 'database_2'
       AND c2.TABLE_NAME = wt.table_name
    LEFT JOIN information_schema.COLUMNS c1
        ON c1.TABLE_SCHEMA = 'database_1'
       AND c1.TABLE_NAME = wt.table_name
       AND c1.ORDINAL_POSITION = c2.ORDINAL_POSITION
    WHERE c1.ORDINAL_POSITION IS NULL
),
 
/* ============================================================
   INDICI
   ============================================================ */
indexes_database_1 AS (
    SELECT
        TABLE_NAME,
        INDEX_NAME,
        CONCAT(
            NON_UNIQUE, '|',
            INDEX_TYPE, '|',
            GROUP_CONCAT(
                CONCAT(
                    SEQ_IN_INDEX, ':',
                    COALESCE(COLUMN_NAME, '<NULL>'), ':',
                    COALESCE(COLLATION, '<NULL>'), ':',
                    COALESCE(CARDINALITY, '<NULL>')
                )
                ORDER BY SEQ_IN_INDEX
                SEPARATOR ','
            )
        ) AS definition
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = 'database_1'
      AND TABLE_NAME IN (SELECT table_name FROM wanted_tables)
    GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE, INDEX_TYPE
),
 
indexes_database_2 AS (
    SELECT
        TABLE_NAME,
        INDEX_NAME,
        CONCAT(
            NON_UNIQUE, '|',
            INDEX_TYPE, '|',
            GROUP_CONCAT(
                CONCAT(
                    SEQ_IN_INDEX, ':',
                    COALESCE(COLUMN_NAME, '<NULL>'), ':',
                    COALESCE(COLLATION, '<NULL>'), ':',
                    COALESCE(CARDINALITY, '<NULL>')
                )
                ORDER BY SEQ_IN_INDEX
                SEPARATOR ','
            )
        ) AS definition
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = 'database_2'
      AND TABLE_NAME IN (SELECT table_name FROM wanted_tables)
    GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE, INDEX_TYPE
),
 
indexes_cmp AS (
    SELECT
        COALESCE(i1.TABLE_NAME, i2.TABLE_NAME) AS table_name,
        COALESCE(i1.INDEX_NAME, i2.INDEX_NAME) AS index_name,
        COALESCE(i1.definition, '<MISSING>') AS definition_database_1,
        COALESCE(i2.definition, '<MISSING>') AS definition_database_2
    FROM indexes_database_1 i1
    LEFT JOIN indexes_database_2 i2
        ON i2.TABLE_NAME = i1.TABLE_NAME
       AND i2.INDEX_NAME = i1.INDEX_NAME
 
    UNION ALL
 
    SELECT
        i2.TABLE_NAME,
        i2.INDEX_NAME,
        '<MISSING>',
        i2.definition
    FROM indexes_database_2 i2
    LEFT JOIN indexes_database_1 i1
        ON i1.TABLE_NAME = i2.TABLE_NAME
       AND i1.INDEX_NAME = i2.INDEX_NAME
    WHERE i1.INDEX_NAME IS NULL
),
 
/* ============================================================
   FOREIGN KEY
   ============================================================ */
fk_database_1 AS (
    SELECT
        kcu.TABLE_NAME,
        kcu.CONSTRAINT_NAME,
        CONCAT(
            kcu.REFERENCED_TABLE_SCHEMA, '.',
            kcu.REFERENCED_TABLE_NAME, '|',
            GROUP_CONCAT(
                CONCAT(
                    kcu.ORDINAL_POSITION, ':',
                    kcu.COLUMN_NAME, '->',
                    kcu.REFERENCED_COLUMN_NAME
                )
                ORDER BY kcu.ORDINAL_POSITION
                SEPARATOR ','
            ),
            '|',
            rc.UPDATE_RULE, '|',
            rc.DELETE_RULE
        ) AS definition
    FROM information_schema.KEY_COLUMN_USAGE kcu
    LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS rc
        ON rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
       AND rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
       AND rc.TABLE_NAME = kcu.TABLE_NAME
    WHERE kcu.CONSTRAINT_SCHEMA = 'database_1'
      AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
      AND kcu.TABLE_NAME IN (SELECT table_name FROM wanted_tables)
    GROUP BY
        kcu.TABLE_NAME,
        kcu.CONSTRAINT_NAME,
        rc.UPDATE_RULE,
        rc.DELETE_RULE,
        kcu.REFERENCED_TABLE_SCHEMA,
        kcu.REFERENCED_TABLE_NAME
),
 
fk_database_2 AS (
    SELECT
        kcu.TABLE_NAME,
        kcu.CONSTRAINT_NAME,
        CONCAT(
            kcu.REFERENCED_TABLE_SCHEMA, '.',
            kcu.REFERENCED_TABLE_NAME, '|',
            GROUP_CONCAT(
                CONCAT(
                    kcu.ORDINAL_POSITION, ':',
                    kcu.COLUMN_NAME, '->',
                    kcu.REFERENCED_COLUMN_NAME
                )
                ORDER BY kcu.ORDINAL_POSITION
                SEPARATOR ','
            ),
            '|',
            rc.UPDATE_RULE, '|',
            rc.DELETE_RULE
        ) AS definition
    FROM information_schema.KEY_COLUMN_USAGE kcu
    LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS rc
        ON rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
       AND rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
       AND rc.TABLE_NAME = kcu.TABLE_NAME
    WHERE kcu.CONSTRAINT_SCHEMA = 'database_2'
      AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
      AND kcu.TABLE_NAME IN (SELECT table_name FROM wanted_tables)
    GROUP BY
        kcu.TABLE_NAME,
        kcu.CONSTRAINT_NAME,
        rc.UPDATE_RULE,
        rc.DELETE_RULE,
        kcu.REFERENCED_TABLE_SCHEMA,
        kcu.REFERENCED_TABLE_NAME
),
 
fk_cmp AS (
    SELECT
        COALESCE(f1.TABLE_NAME, f2.TABLE_NAME) AS table_name,
        COALESCE(f1.CONSTRAINT_NAME, f2.CONSTRAINT_NAME) AS constraint_name,
        COALESCE(f1.definition, '<MISSING>') AS definition_database_1,
        COALESCE(f2.definition, '<MISSING>') AS definition_database_2
    FROM fk_database_1 f1
    LEFT JOIN fk_database_2 f2
        ON f2.TABLE_NAME = f1.TABLE_NAME
       AND f2.CONSTRAINT_NAME = f1.CONSTRAINT_NAME
 
    UNION ALL
 
    SELECT
        f2.TABLE_NAME,
        f2.CONSTRAINT_NAME,
        '<MISSING>',
        f2.definition
    FROM fk_database_2 f2
    LEFT JOIN fk_database_1 f1
        ON f1.TABLE_NAME = f2.TABLE_NAME
       AND f1.CONSTRAINT_NAME = f2.CONSTRAINT_NAME
    WHERE f1.CONSTRAINT_NAME IS NULL
)
 
/* ============================================================
   RISULTATO
   ============================================================ */
 
SELECT
    'TABLE' AS tipo,
    table_name AS oggetto,
    definition_database_1 AS database_1,
    definition_database_2 AS database_2
FROM tables_cmp
WHERE NOT (
    exists_database_1 <=> exists_database_2
    AND definition_database_1 <=> definition_database_2
)
 
UNION ALL
 
SELECT
    'COLUMN' AS tipo,
    CONCAT(table_name, '.', COALESCE(column_database_1, column_database_2),
           ' [pos=', pos, ']') AS oggetto,
    definition_database_1 AS database_1,
    definition_database_2 AS database_2
FROM columns_cmp
WHERE definition_database_1 <> definition_database_2
   OR (definition_database_1 IS NULL AND definition_database_2 IS NOT NULL)
   OR (definition_database_1 IS NOT NULL AND definition_database_2 IS NULL)
 
UNION ALL
 
SELECT
    'INDEX' AS tipo,
    CONCAT(table_name, '.', index_name) AS oggetto,
    definition_database_1 AS database_1,
    definition_database_2 AS database_2
FROM indexes_cmp
WHERE NOT (definition_database_1 <=> definition_database_2)
 
UNION ALL
 
SELECT
    'FOREIGN KEY' AS tipo,
    CONCAT(table_name, '.', constraint_name) AS oggetto,
    definition_database_1 AS database_1,
    definition_database_2 AS database_2
FROM fk_cmp
WHERE NOT (definition_database_1 <=> definition_database_2)
 
ORDER BY tipo, oggetto;