Artful Common Queries

Compare structures of two tables

from the Artful Common Queries page

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 ;

Last updated 21 Mar 2024