Another flavor to 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. > -----Original Message----- > From: Daevid Vincent [mailto:dae...@daevid.com] > Sent: Monday, July 08, 2013 7:57 PM > To: mysql@lists.mysql.com > Subject: RE: Need query to determine different column definitions across > tables > > > > > -----Original Message----- > > From: Daevid Vincent [mailto:dae...@daevid.com] > > Sent: Monday, July 08, 2013 2:11 PM > > To: mysql@lists.mysql.com > > Subject: Need query to determine different column definitions across > tables > > > > 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. > > Well, here's the query I'm using currently. Will post updates as I tweak > it. > > USE `information_schema`; > > SELECT > t1.`COLUMN_NAME`, > t1.`TABLE_NAME`, > t1.`COLUMN_TYPE`, > -- CONCAT(t1.`TABLE_NAME`,'.',t1.`COLUMN_TYPE`) as t1_type, > t2.`TABLE_NAME`, > t2.`COLUMN_TYPE` > -- CONCAT(t2.`TABLE_NAME`,'.',t2.`COLUMN_TYPE`) AS t2_type FROM > `COLUMNS` AS t1 > LEFT JOIN `COLUMNS` AS t2 > ON t1.`COLUMN_NAME` = t2.`COLUMN_NAME` > AND t1.`COLUMN_TYPE` <> t2.`COLUMN_TYPE` > WHERE t1.`TABLE_SCHEMA` = 'mydatabase' > AND t2.`TABLE_NAME` IS NOT NULL > -- HAVING t2_type IS NOT NULL > ORDER BY `COLUMN_NAME` ASC; > > Having separate columns there is easier to read/compare than CONCAT() I > think. > > Another bulk version that comes in handy: > > SELECT `COLUMN_NAME`, `COLUMN_TYPE`, `TABLE_SCHEMA`, > `TABLE_NAME`, `CHARACTER_SET_NAME`, `COLLATION_NAME` > FROM `COLUMNS` WHERE `TABLE_SCHEMA` = ' mydatabase ' > ORDER BY `COLUMN_NAME`; > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql