mysql: compare structure of two tables

The following (untested) SQL should give you a list of columns in both tables.
The columns in_table_1 and in_table_2 will contain 'Yes' if the column exists in that table.

select column_name
      ,max(case when table_name = 'table_1' then 'Yes' end) as in_table_1
      ,max(case when table_name = 'table_2' then 'Yes' end) as in_table_2
  from information_schema.columns
 where table_name in('table_1', 'table_2')
   and table_schema = 'your_database'
 group
    by column_name
 order
    by column_name;

You could add having count(*) = 1 to only return the columns that aren't in both tables.

You probably want to add datatypes as well. Have a look at the the INFORMATION_SCHEMA

Tags:

Mysql