List:General Discussion« Previous MessageNext Message »
From:Aleksander Dye Date:September 29 2006 5:32am
Subject:RE: comparing two databases
View as plain text  
I'm using navicat (www.navicat.com) which is a great tool (has a 30 day trial and not that
high a cost. 

Regards,
Aleksander Dye


-----Original Message-----
From: Peter Brawley [mailto:peter.brawley@stripped]
Sent: 28. september 2006 22:27
To: Steve Buehler; mysql@stripped
Subject: Re: comparing two databases


Steve

 >Is there a program out there that I can use to compare two
 >databases?  Just the structure, not the content.

Here is a query that you might be able to twist into giving you what you 
want. Given two dbs @db1 & @db2, it lists structure diffs between them:

SELECT 
  MIN(table_name) as TableName, 
  table_catalog,table_schema,table_name,column_name,
  ordinal_position,column_default,is_nullable,
  data_type,character_maximum_length,character_octet_length,
  numeric_precision,numeric_scale,character_set_name,
  collation_name,column_type,column_key,
  extra,privileges,column_comment
FROM (
  SELECT 'Table a' as TableName, 
  table_catalog,table_schema,table_name,column_name,
  ordinal_position,column_default,is_nullable,
  data_type,character_maximum_length,character_octet_length,
  numeric_precision,numeric_scale,character_set_name,
  collation_name,column_type,column_key,
  extra,privileges,column_comment
  FROM information_schema.columns c1
  WHERE table_schema=@db1
  UNION ALL
  SELECT 'Table a' as TableName, 
  table_catalog,table_schema,table_name,column_name,
  ordinal_position,column_default,is_nullable,
  data_type,character_maximum_length,character_octet_length,
  numeric_precision,numeric_scale,character_set_name,
  collation_name,column_type,column_key,
  extra,privileges,column_comment
  FROM information_schema.columns c1
  WHERE table_schema=@db2
) AS tmp
GROUP BY tablename,
  table_catalog,table_schema,column_name,
  ordinal_position,column_default,is_nullable,
  data_type,character_maximum_length,character_octet_length,
  numeric_precision,numeric_scale,character_set_name,
  collation_name,column_type,column_key,
  extra,privileges,column_comment
HAVING COUNT(*) = 1
ORDER BY tablename,column_name;

PB

-----

Steve Buehler wrote:
> Is there a program out there that I can use to compare two databases?  
> Just the structure, not the content.
>
> Thanks
> Steve
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.407 / Virus Database: 268.12.9/458 - Release Date: 9/27/2006


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
comparing two databasesSteve Buehler28 Sep
  • Re: comparing two databasesDan Nelson28 Sep
    • Joining *3* tablesRenito 7330 Sep
      • Re: Joining *3* tablesAndré Hänsel30 Sep
  • Re: comparing two databasesCory Robin28 Sep
  • Re: comparing two databasesPeter Brawley28 Sep
    • RE: comparing two databasesAleksander Dye29 Sep
  • Re: comparing two databasesMartijn Tonies29 Sep
  • Re: comparing two databasesmark addison29 Sep
RE: comparing two databasesJohn Trammell28 Sep
RE: comparing two databasesAndrew Braithwaite29 Sep
Re: comparing two databasesSteve Buehler1 Oct