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
