I have a two tables. values inside them are not identical, but most of the strcuture is the same, one of the tables has some extra fields in them.
in a simplified form I have something like this:
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