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
