From: Peter Brawley
Date: August 26 2005 6:54pm
Subject: Re: Compare two tables
List-Archive: http://lists.mysql.com/mysql/188366
Message-Id: <430F656B.3000002@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Alfredo,

If you are using MySQL 5.02 or later, here is an information_schema 
query that will list table structure _differences_. The idea is to group 
the UNION of rows in information_schema.columns for the two tables on 
all information_schema.columns columns, then use HAVING to pick only 
those rows where COUNT(*) is 1, that is, where any column of one 
Information_schema.columns row differs from its joined mate. Edit 'DB1', 
'DB2', 'TABLE1' and 'TABLE2' for your db(s) and tables.

SELECT
  MIN(TableName) AS TableName,
  column_name,
  ordinal_position,
  column_default,
  is_nullable,
  data_type,
  character_maximum_length,
  numeric_precision,
  numeric_scale,
  character_set_name,
  collation_name,
  column_type,
  column_key,
  extra,
  privileges,
  column_comment
FROM (
  SELECT
    'TABLE1' as TableName,
    column_name,
    ordinal_position,
    column_default,
    is_nullable,
    data_type,
    character_maximum_length,
    numeric_precision,
    numeric_scale,
    character_set_name,
    collation_name,
    column_type,
    column_key,
    extra,
    privileges,
    column_comment
  FROM information_schema.columns AS i1
  WHERE table_schema='DB1' AND table_name='TBL1'
  UNION ALL
  SELECT
    'TABLE2' as TableName,
    column_name,
    ordinal_position,
    column_default,
    is_nullable,
    data_type,
    character_maximum_length,
    numeric_precision,
    numeric_scale,
    character_set_name,
    collation_name,
    column_type,
    column_key,
    extra,
    privileges,
    column_comment 
  FROM information_schema.columns AS i2
  WHERE table_schema='DB2' AND table_name='TABLE2'
) AS tmp
GROUP BY column_name
HAVING COUNT(*) = 1
ORDER BY column_name ;

http://www.artfulsoftware.com

PB

-----

Alfredo Cole wrote:

>Hi:
>
>I need to compare the structure of two tables (fields, field types, field 
>lengths, indices, etc.) to determine if they have the same schema, even if 
>the fields may be in a different order. Is there a command in mysql that will 
>do this? This will be used to determine if the tables are basically the same, 
>or if they need to be upgraded based on the table structures of a central 
>office.
>
>Thank you.
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.16/83 - Release Date: 8/26/2005