I am comparing database tables on a development server against a live server, looking for column name changes, new columns, and columns that have been dropped. I'd like to do something like this:
SELECT GROUP_CONCAT(Field) FROM (SHOW COLUMNS ON table_name) GROUP BY Field
SHOW COLUMNS FROM table_name WHERE NOT IN ([comma-delimited list from above query])
SELECT CONCAT("'", GROUP_CONCAT(column_name ORDER BY ordinal_position SEPARATOR "', '"), "'") AS columns
WHERE table_schema = 'db_name' AND table_name = 'tbl_name'
'id', 'name', 'field1', 'field2'
SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position)
WHERE table_schema = 'db_name' AND table_name = 'tbl_name' AND column_name NOT IN ('id', 'name', 'field1', 'field2')
Take a look at the information_schema.columns table
select group_concat(column_name order by ordinal_position) from information_schema.columns where table_schema = 'database_name' and table_name = 'table_name'
edit. Information schema allows you to make queries on metadata. So, you can even compare fields between tables with a left join for example.
edit. Hi Chris. Glad you've solved. As you said your problem was quite different because it concerned with different servers. I add an example of two different databases on the same server.
create database db1; use db1; create table table1( id int not null auto_increment primary key, name varchar(50), surname varchar(50), dob date) engine = myisam; create database db2; create table db2.table2 like db1.table1; alter table db2.table2 drop column dob; select i1.column_name from ( select column_name from information_schema.columns where table_schema = 'db1' and table_name = 'table1' ) as i1 left join ( select column_name from information_schema.columns where table_schema = 'db2' and table_name = 'table2' ) as i2 on i1.column_name = i2.column_name where i2.column_name is null
and the obvious result is dob that is present in table1 and not in table2.
Hope that it helps someone else. Regards guys. :)