Here's a query outline for listing structure differences between any two tables. It selects all information_schema.columns rows for one table, does the same for the second table, UNIONs these two queries, then uses HAVING to pick only those rows where the COUNT(*) in the union is 1—that is, where a column of one table differs from its mate.
SELECT
MIN(TableName) AS 'Table',
column_name AS 'Column',
ordinal_position AS 'Position','
data_type
FROM (
SELECT 't1' as TableName, column_name, ordinal_position, data_type
FROM information_schema.columns AS i1
WHERE table_schema='test' AND table_name='t1'
UNION ALL
SELECT 't2' as TableName, column_name, ordinal_position, data_type
FROM information_schema.columns AS i2
WHERE table_schema='test' AND table_name='t2'
) AS tmp
GROUP BY column, position, data_type
HAVING COUNT(*) = 1
ORDER BY ordinal_position;
To capture all structure differences between two tables, and to avoid having to cut and paste database and table names, save it as a stored procedure. We keep all such routines in a system database.
DROP PROCEDURE IF EXISTS CompareTableStructs;
DELIMITER |
CREATE PROCEDURE CompareTableStructs (
IN db1 VARCHAR(64), IN tbl1 VARCHAR(64), IN db2 VARCHAR(64), IN tbl2 VARCHAR(64)
)
SELECT
MIN(TableName) AS TableName,
column_name, ordinal_position, column_default, is_nullable,
data_type, character_maximum_length, numeric_precision,
numeric_scale, character_set_name, collation_name,
column_type, column_key, extra, privileges, column_comment
FROM (
SELECT
tbl1 as TableName,
column_name, ordinal_position, column_default, is_nullable,
data_type, character_maximum_length, numeric_precision,
numeric_scale, character_set_name, collation_name,
column_type, column_key, extra, privileges, column_comment
FROM information_schema.columns AS i1
WHERE table_schema=db1 AND table_name=tbl1
UNION ALL
SELECT
tbl2 as TableName,
column_name, ordinal_position, column_default, is_nullable,
data_type, character_maximum_length, numeric_precision,
numeric_scale, character_set_name, collation_name,
column_type, column_key, extra, privileges, column_comment
FROM information_schema.columns AS i2
WHERE table_schema=db2 AND table_name=tbl2
) AS tmp
GROUP BY
column_name, ordinal_position, column_default, is_nullable,
data_type, character_maximum_length, numeric_precision,
numeric_scale, character_set_name, collation_name,
column_type, column_key, extra, privileges, column_comment
HAVING COUNT(*) = 1
ORDER BY column_name ;
|
DELIMITER ;
|
|