How to compare schema
If you have receive ddl statements from your developer and you want to check if it matches the current state of the development database, because the developer have done a lot of change in a quick and undocumented manner, what are your options?
I found this handy feature in Toad :
1) I create my objects on a separate database with the ddl I received from development
2) I compare the schema they use with the schema I created in Toad
–> Database –> Compare –> Schema
I select the options I want:
–> functions, indexes, packages, procedures, triggers, tables, view
I select the Reference and Comparison connections/schemas. Then I click compare
3) I receive the result
(only) 29 differences
4) the real bonus, I receive a script to update the live data according to the script I received. Undocumented change should never happen, so I do some communication with the developers
drop index foo;
drop table bar;
alter table gaz drop column bop;
alter table gaz modify (quux null);
this is not going to be blind-executable, some change are simply impossible to implement, but for my little test, I was happy to discover that function
I have been using ERwin for this purpose before, but the version I have (4.1) is very buggy and does not support a lot of syntaxes (ex: deferred constraints, create view v as select cast(1 as number(1)) x from dual, etc…). Also ERwin can compare only with the current model, so no direct comparison between 2 database schema.
August 14th, 2007 at 17:35
Welcome to TOAD/GUI’s world
August 15th, 2007 at 09:49
something strange is that in the sync script, I can only print, but not save or copypaste or execute
August 15th, 2007 at 11:30
Nice tip, thank you for sharing.
Related to the topic I believe, have you got any experience on Toad’s answer to sql and pl/sql release management?
Best regards.
August 15th, 2007 at 13:03
never heard about that, sounds good
August 15th, 2007 at 14:08
something strange is that in the sync script, I can only print, but not save or copypaste or execute
Strange. I have been using the compare tool of TOAD for a long time without problems. I find it really useful.
August 21st, 2007 at 10:41
I found the reason :
Note : Script is view-only because you do not have access to dba module functions in TOAD
extremly annoying
even print to pdf than copy paste is not possible for the script
August 27th, 2007 at 15:21
Yep — this is nice. There’s something similar in Enterprise Manager. We did this all the time after installing / promoting changes from Dev to Test to QA to Prod. Usually as a sanity check to make sure we promoted everything, but also as a way to make sure we could explain why there were any differences. It’s also nice to ignore / check the storage clauses.
December 19th, 2007 at 22:08
You can actually get around the need to have the DBA module functions by setting up a Print to file printer with a Generic / Plain Text driver, then printing with that. That does create a file that won’t immediately run as a script though so you need to:
1. Delete the rows inserted between pages (this may be a pain on a large amount of changes)
2. Edit the file in something like ConText, enable column selection mode, then hold ALT while you click and drag between the first few columns to delete the line number prefixes.
3. Clean up the header or just delete it all together.
December 19th, 2007 at 22:25
Well, I cannot test it it as I have full privilege right now, but as I remember print was not working that well…
Thanks for the tip, I keep it in case I have to use a castrated version of toad again
February 5th, 2008 at 16:43
There’s a better workaround - select all text and press ctrl+f - all selected text will be copied to the Find dialog.
February 5th, 2008 at 17:39
Thank you Am ! I may need it one day
February 26th, 2008 at 18:36
Nice work around Am, was trying to find a way to fudge past that DBA Module check. Thanks for the info!
March 10th, 2008 at 17:29
Thanks AM, select all text and press ctrl+f works great.
March 18th, 2008 at 16:33
Hey Am,
The fix works great. Thanks for the valuable tip. Helped us got rid of many inconsistencies!
Cheers!
DJ
March 28th, 2008 at 22:17
great tip
another thing that i am struggling in schema compare of Toad
in the results i see diff of functions, procedures etc etc but not table differences - any idea’s why that could be ?
April 17th, 2008 at 12:56
Am - worth a blog post to it’s self, that tip has saved my morning !
April 18th, 2008 at 22:15
Great!! thanks a bunch!! Ctrl + F works great!
May 22nd, 2008 at 20:27
Hi Amy,
I was looking for a workaround for the DBA Module requirement, and was very excited to learn about this new trick, but unfortunately it’s not working for me. When I select all text and press Ctrl+F, just the regular find dialog box appears with options to find and the script is not copied anywhere. Can you please advise if I am doing anything diffrent?
Thanks!
May 22nd, 2008 at 20:28
Am,
Very sorry for the typo in the name…
August 1st, 2008 at 13:35
I found that select all and Ctrl+ X works great.