RE: Need query to determine different column definitions across tables
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
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
RE: Need query to determine different column definitions across tables
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. > -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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
