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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
