MySQL: trovare le differenze negli schemi tra due tabelle in due database diversi

Uno snippet veloce per trovare le differenze tra due tabelle in due database diversi (molto utile quando ci si è persi qualche modifica!).

E' sufficiente modificare le variabili database_1, database_2 e table_1 per avere la lista delle differenze:

SELECT table_schema,table_name,column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        table_schema,table_name,column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE
    (
        (table_schema='database_1' AND table_name='table_1') OR
        (table_schema='database_2' AND table_name='table_1')
    )
    AND table_name IN ('table_1')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A

 

 

Fonte: https://dba.stackexchange.com/questions/75532/query-to-compare-the-structure-of-two-tables-in-mysql

 


Commenti