top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration
Print Preview

Need query to determine different column definitions across tables using MySql

0 votes
34 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 Google+ Share Button LinkedIn Share Button Multiple Social 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

Useful Links with Similar Problem
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#470/147, 3rd Floor, 5th Main,
HSR Layout Sector 7,
Bangalore - 560102,
Karnataka INDIA.
QUERY HOME
...