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.
Welcome to TOAD/GUI’s world
something strange is that in the sync script, I can only print, but not save or copypaste or execute
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.
never heard about that, sounds good
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.
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
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.
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.
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
There’s a better workaround - select all text and press ctrl+f - all selected text will be copied to the Find dialog.
Thank you Am ! I may need it one day
Nice work around Am, was trying to find a way to fudge past that DBA Module check. Thanks for the info!
Thanks AM, select all text and press ctrl+f works great.
Hey Am,
The fix works great. Thanks for the valuable tip. Helped us got rid of many inconsistencies!
Cheers!
DJ
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 ?
Am - worth a blog post to it’s self, that tip has saved my morning !
Great!! thanks a bunch!! Ctrl + F works great!
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!
Am,
Very sorry for the typo in the name…
I found that select all and Ctrl+ X works great.
select all and Ctrl+ X works for me. better than select all and Ctrl+F