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
What I am after is a comma-delimited list that I can then take to the live server and do:
SHOW COLUMNS FROM table_name WHERE NOT IN ([comma-delimited list from above query])
Any thoughts on how best to do this – either by correcting me in my own approach, or by another means all together? Obviously, the above SQL does not work.
A note: The servers are entirely separate and may not communicate with each other, so no direct comparison is possible.
Thanks for the answers, guys! Applying your answers to the question, this is my final SQL to get the column names:
SELECT CONCAT("'", GROUP_CONCAT(column_name ORDER BY ordinal_position SEPARATOR "', '"), "'") AS columns FROM information_schema.columns WHERE table_schema = 'db_name' AND table_name = 'tbl_name'
That gives me a list that looks like this:
'id', 'name', 'field1', 'field2'
Then I can use this query to compare:
SELECT GROUP_CONCAT(column_name ORDER BY ordinal_position) FROM information_schema.columns WHERE table_schema = 'db_name' AND table_name = 'tbl_name' AND column_name NOT IN ('id', 'name', 'field1', 'field2')
The results are a list of any columns that exist in the first database and not in the second. Perfect!
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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. 🙂
You should use
You can copy the
information_schema.columns table from each database into a shared schema and then run SQL queries to compare them.