top button
Flag Notify
Site Registration

Need query to determine different column definitions across tables using MySql

0 votes
311 views

I'm noticing that across our several databases and hundreds of tables that column definitions are not consistent. I'm wondering if there is a tool or query (using INFORMATION_SCHEMA perhaps) that will show me all databases, tables and columns where they don't match (by column name).

For example in one table foo_id might be UNSIGNED and in other's it is not. Or maybe it's INT(11) in some and INT(10) or worse MEDIUMINT in others. Or extending further Charset/Collation might mismatch and be that stupid "latin1_swedish_ci" and fixed to be "utf8" in others.

Stuff like that. I want to see everything where there is some difference.

posted Jul 8, 2013 by anonymous

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+1 vote

Try:

SELECT COLUMN_NAME,
 group_concat(db_tbl SEPARATOR ' ') as db_tbls,
 group_concat(DISTINCT info SEPARATOR ' | ') as infos
 FROM (
 SELECT COLUMN_NAME,
 concat(TABLE_SCHEMA, '.', TABLE_NAME) as db_tbl,
 concat(COLUMN_TYPE, ' ', CHARACTER_SET_NAME) as info
 FROM `COLUMNS`
 WHERE TABLE_SCHEMA = 'test' -- optionally restrict to a db
 ) x
 GROUP BY COLUMN_NAME
 HAVING infos LIKE '%|%';

Notice how it uses GROUP_CONCAT() and HAVING to do the filtering.

answer Jul 10, 2013 by anonymous
0 votes

See if you like this:

SELECT TABLE_SCHEMA as db, TABLE_NAME, COLUMN_NAME,
 CHARACTER_SET_NAME, COLUMN_TYPE
 FROM `COLUMNS`
 ORDER BY 3,4,5;

You might be able to embellish on it to avoid consistent definitions, etc.

answer Jul 9, 2013 by anonymous
...