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;