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
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.
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
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
