[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    RE: Need query to determine different column definitions across tables
From:       Rick James <rjames () yahoo-inc ! com>
Date:       2013-07-10 1:05:48
Message-ID: 582AFBFC517D194489EF570FE21694CF141EAF1E () GQ1-MB04-02 ! y ! corp ! yahoo ! com
[Download RAW message or body]

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:daevid@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:daevid@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

[prev in list] [next in list] [prev in thread] [next in thread]