Re: Compare two tables
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]
Re: Compare two tables
> > You could check the table DDL. > > > > Or use a third party tool, like Database Workbench, that can do this for > > you and even generator a change script. Check www.upscene.com > > > > With regards, > > > > Martijn Tonies > > Hmmm. No Linux version. Thank you anyway. Nope, indeed. Serious answer: too few Linux desktops. It has been reported to run fine under Wine or Win4Lin though. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compare two tables
El Viernes, 26 de Agosto de 2005 08:56, Gordon Bruce escribió: > If you have the 5.0.x version of MySQL then INFROMATION SCHEMA can give > you what you want. i.e. > > SELECT a.*, b.* > FROM INFORMATION_SCHEMA.COLUMNS AS a >INNER JOIN _SCHEMA.COLUMNS AS b >ON (a.column_name = b.column_name) > WHERE a.TABLE_NAME = 'foo_1' >AND b.TABLE_NAME = 'foo_2' > > If you look up INFORMATION SCHEMA in the documentation you will find the > table definitions to chose the columns you need for your comparison. > > 21. The INFORMATION_SCHEMA Information Database > 21.1. INFORMATION_SCHEMA Tables > This is very interesting. I'm using 4.1.12 as it is the stable version, but I will keep an eye on version 5. Thank you. -- Alfredo J. Cole Grupo ACyC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compare two tables
On Fri, 2005-08-26 at 09:14 -0600, Alfredo Cole wrote: > El Viernes, 26 de Agosto de 2005 08:16, Martijn Tonies escribió: > > > > You could check the table DDL. > > > > Or use a third party tool, like Database Workbench, that can do this for > > you and even generator a change script. Check www.upscene.com > > > > With regards, > > > > Martijn Tonies > > Hmmm. No Linux version. Thank you anyway. lol! You could give SqlFairy a go, it comes with a sqlt-diff command that should do want you want. See:http://sqlfairy.sourceforge.net/ Or, as its perl, you can just do 'install SQL::Translator' in a CPAN shell. mark -- This email (and any attachments) is intended solely for the individual(s) to whom addressed. It may contain confidential and/or legally privileged information. Any statement or opinions therein are not necessarily those of ITN unless specifically stated. Any unauthorised use, disclosure or copying is prohibited. If you have received this email in error, please notify the sender and delete it from your system. Security and reliability of the e-mail and attachments are not guaranteed. You must take full responsibility for virus checking. Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read e-mails sent to and from our server(s). Independent Television News Limited, Registered No. 548648 England, VAT Reg. No: GB 756 2995 81, 200 Gray's Inn Road, London WC1X 8XZ, Telephone: 020 7833 3000. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compare two tables
El Viernes, 26 de Agosto de 2005 08:16, Martijn Tonies escribió: > > You could check the table DDL. > > Or use a third party tool, like Database Workbench, that can do this for > you and even generator a change script. Check www.upscene.com > > With regards, > > Martijn Tonies Hmmm. No Linux version. Thank you anyway. -- Alfredo J. Cole Grupo ACyC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Compare two tables
If you have the 5.0.x version of MySQL then INFROMATION SCHEMA can give you what you want. i.e. SELECT a.*, b.* FROM INFORMATION_SCHEMA.COLUMNS AS a INNER JOIN _SCHEMA.COLUMNS AS b ON (a.column_name = b.column_name) WHERE a.TABLE_NAME = 'foo_1' AND b.TABLE_NAME = 'foo_2' If you look up INFORMATION SCHEMA in the documentation you will find the table definitions to chose the columns you need for your comparison. 21. The INFORMATION_SCHEMA Information Database 21.1. INFORMATION_SCHEMA Tables -Original Message- From: Alfredo Cole [mailto:[EMAIL PROTECTED] Sent: Friday, August 26, 2005 9:08 AM To: mysql@lists.mysql.com Subject: Compare two tables 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. -- Alfredo J. Cole Grupo ACyC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compare two tables
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. You could check the table DDL. Or use a third party tool, like Database Workbench, that can do this for you and even generator a change script. Check www.upscene.com With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compare two tables
Alfredo Cole <[EMAIL PROTECTED]> wrote on 08/26/2005 10:07:30 AM: > 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. > > -- > Alfredo J. Cole > Grupo ACyC > No, there is no built-in command to make this kind of comparison. However the family of SHOW ... commands (SHOW CREATE TABLE, SHOW COLUMNS, etc.) will really help you to do this kind of comparison by hand. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
