Need query to determine different column definitions across tables using MySql

Question

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.

Answer 1

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 2

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.