List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 26 2005 6:54pm
Subject:Re: Compare two tables
View as plain text  
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

Thread
Compare two tablesAlfredo Cole26 Aug
  • Re: Compare two tablesSGreen26 Aug
  • Re: Compare two tablesMartijn Tonies26 Aug
    • Re: Compare two tablesAlfredo Cole26 Aug
      • Re: Compare two tablesMark Addison26 Aug
  • Re: Compare two tablesMartijn Tonies26 Aug
  • Re: Compare two tablesPeter Brawley26 Aug
RE: Compare two tablesGordon Bruce26 Aug
  • Re: Compare two tablesAlfredo Cole26 Aug