Query below compares columns (names) in tables between two MySQL databases (schemas). It shows columns missing in either of two schema.

Query

set @database_1 = 'database_name_1'; -- provide first database name here
set @database_2 = 'database_name_2'; -- provide second database name here
select * 
from (
        select COALESCE(c1.table_name, c2.table_name) as table_name,
               COALESCE(c1.column_name, c2.column_name) as table_column,
               c1.column_name as database1,
               c2.column_name as database2
        from
            (select table_name,
                    column_name
             from information_schema.columns c
             where c.table_schema = @database_1) c1
        right join
                 (select table_name,
                         column_name
                  from information_schema.columns c
                  where c.table_schema = @database_2) c2
        on c1.table_name = c2.table_name and c1.column_name = c2.column_name

    union

        select COALESCE(c1.table_name, c2.table_name) as table_name,
               COALESCE(c1.column_name, c2.column_name) as table_column,
               c1.column_name as schema1,
               c2.column_name as schema2
        from
            (select table_name,
                    column_name
             from information_schema.columns c
             where c.table_schema = @database_1) c1
        left join
                 (select table_name,
                         column_name
                  from information_schema.columns c
                  where c.table_schema = @database_2) c2
        on c1.table_name = c2.table_name and c1.column_name = c2.column_name
) tmp
where database1 is null
      or database2 is null
order by table_name,
         table_column;
set @database_1 = null;
set @database_2 = null;

Instruction

Replace database_name_1 and database_name_2 with names of two of your databases (schemas) (on MySQL instance) that you'd like to compare.

Columns

• table_name - name of the table
• table_column - name of column
• database1 - if column exists in a table in database1 (schema) then column contains its name (repeats it from column column)
• database2 - if column exists in a table in database2 (schema) then column contains its name (repeats it from column column)

Rows

• One row represents one distinct name of column in specific table. Row represents column if it exist only in one schema.
• Scope of rows: all distinct schema, table and column names in both databases.
• Ordered by table name and column name

Sample results