List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:July 9 2013 2:57am
Subject:RE: Need query to determine different column definitions across tables
View as plain text  

> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@stripped]
> Sent: Monday, July 08, 2013 2:11 PM
> To: mysql@stripped
> 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`;


Thread
Need query to determine different column definitions across tablesDaevid Vincent8 Jul
  • RE: Need query to determine different column definitions acrosstablesRick James9 Jul
  • RE: Need query to determine different column definitions across tablesDaevid Vincent9 Jul
    • RE: Need query to determine different column definitions acrosstablesRick James10 Jul