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.

33 thoughts on “How to compare schema”

  3. TongucY

    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.

  5. Yas

    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.

  6. Laurent Schneider Post author

    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

  7. Dominic Delmolino

    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.

  8. Jason Bainbridge

    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.

  9. Laurent Schneider Post author

    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

  10. Am

    There’s a better workaround – select all text and press ctrl+f – all selected text will be copied to the Find dialog.

  12. Jay Kirk

    Nice work around Am, was trying to find a way to fudge past that DBA Module check. Thanks for the info!

  14. DJ

    Hey Am,

    The fix works great. Thanks for the valuable tip. Helped us got rid of many inconsistencies!

    Cheers!
    DJ

  15. am2

    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 ?

  18. AnotherToadUser

    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!

  25. Pingback: Laurent Schneider » package version control

  27. mayur

    @Laurent Schneider
    to copy the the script in sync script do this…

    1) ctrl + a (to select all)
    2) ctrl + f (will put all script into find)
    3) ctrl + c (copy the script from find)
    4) open a new editor and ctrl + v(paste the copied script into new editor).

    Thanks to a member below to share this info..

  28. Bev Slater

    Excellent workaround. I wanted to buy the DBA module simply to be able to edit the sync script. ctrl + a,f,c,v works like a charm.

  32. total_newb

    Both ctrl + a,f,c,v and ctrl+x worked beautifully. Thanks Am and Funmi A. Even if you don’t see the anything in the “Text to find” box just hit ctrl +c,v and you should be able to paste the script into another text editor. I use Notepad++, by the way, which is an excellent opensource text editor.

Comments are closed.