List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:September 28 2006 8:26pm
Subject:Re: comparing two databases
View as plain text  
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

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