# Compare schema improvement (suggestion) **URL:** \ **Category:** Toad for Oracle - Beta\ **Created:** [October 8, 2013, 12:40am UTC](https://forums.toadworld.com/t/compare-schema-improvement-suggestion/27025 "2013-10-08T00:40:46Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [October 8, 2013, 12:40am UTC](https://forums.toadworld.com/t/compare-schema-improvement-suggestion/27025/1 "2013-10-08T00:40:46Z")
Hi, Toad 12.1 x64 (but affected all versions). Here is example of constraint improvement that might help a lot… Many Oracle users write create table statement like: create table t ( COL1 NUMBER NOT NULL, COL2 VARCHAR2(10 BYTE) NOT NULL ); where constrained is automatically named by oracle. When one compare such a table in two schemas, Toad report difference between them regarding this is, looking from Oracle perspective not true. The problem is that Toad compare constraints by name and not constrains by type and columns (what is in fact most important). Similar thing happened with automatic pk constraint which generate unique index by oracle name. Now imagine several hundreds of tables and a result one might get with comparison. So mine proposal for next versions is to implement logic which will be more intelligent in compare such a cases. Brg, Damir Vadas ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [October 8, 2013, 6:22am UTC](https://forums.toadworld.com/t/compare-schema-improvement-suggestion/27025/2 "2013-10-08T06:22:18Z")
I just created this table in two schemas and ran the schema compare against it. On the “object types to compare” tab, I checked only tables and all types of constraints. It reported no differences (as I expected, because I know I put some code in there to compare constraints regardless of name) Could you provide more details about how you are seeing the differences? **From:** damir.vadas\_531 [[mailto:bounce-damirvadas\_531@toadworld.com](mailto:bounce-damirvadas_531@toadworld.com)] **Sent:** Tuesday, October 08, 2013 2:41 AM **To:** [toadoraclebeta@toadworld.com](mailto:toadoraclebeta@toadworld.com) **Subject:** [Toad for Oracle - Beta Discussion Forum] Compare schema improvement (suggestion) **[Compare schema improvement (suggestion)](http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20469.aspx)** Thread created by damir.vadas\_531 Hi, Toad 12.1 x64 (but affected all versions). Here is example of constraint improvement that might help a lot… Many Oracle users write create table statement like: create table t ( COL1 NUMBER NOT NULL, COL2 VARCHAR2(10 BYTE) NOT NULL ); where constrained is automatically named by oracle. When one compare such a table in two sachemas, Toad report difference them regarding this is, looking from Oracle perspective not true. The problem is that Toad compare constraint name and not constrains by type and columns. Similar thing happened with automatic pk constraint which generate unique index by oracle name. Now imagine several hundreds of tables and a result one might get with comparison. So mine proposal for next versions is to implement logic which will be more intelligent in compare such a casses. Brg, Damir Vadas To reply, please reply-all to this email. [Stop receiving emails](http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20469.aspx?subscribe=false) on this subject. Or [ Unsubscribe from Toad for Oracle - Beta](http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86.aspx?subscribe=false) notifications altogether. Toad for Oracle - Beta Discussion Forum [Flag](http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20469.aspx?AbuseContentId=9274ab45-5ec5-4d02-8b60-c6b1e4eed9a2&AbuseContentTypeId=46448885-d0e6-4133-bbfb-f0cd7b0fd6f7&AbuseFlag=true) this post as spam/abuse. ![](http://gyk.r.mailjet.com/74abfa68/57lwpE.gif) ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [October 8, 2013, 9:35am UTC](https://forums.toadworld.com/t/compare-schema-improvement-suggestion/27025/3 "2013-10-08T09:35:27Z")
SQL\> grant connect, resource to u1 identified by u1; Grant succeeded. SQL\> grant connect, resource to u2 identified by u2; Grant succeeded. SQL\> create table u1.t1 ( 2 COL1 NUMBER NOT NULL, 3 COL2 VARCHAR2(10 BYTE) NOT NULL 4 ); Table created. SQL\> create table u2.t1 ( 2 COL1 NUMBER constraint t1\_col1\_nn NOT NULL, 3 COL2 VARCHAR2(10 BYTE) constraint t1\_col2\_nn NOT NULL 4 ); Table created. * * * Now in toad: ![differ.png](https://global.discourse-cdn.com/quest/original/2X/e/ebab95764d4e554fed22ca2d6841467051397b24.png) Now I saw that resulting script sync is empty: * * * ## -- Play this script in U2@XE to make it look like U1@XE ## -- Please review the script before using it to make sure it won't -- cause any unacceptable data loss. ## -- U2@XE Schema Extracted by User TOOLS -- U1@XE Schema Extracted by User TOOLS -- "Set define off" turns off substitution variables Set define off; -- No action taken. Source has system named constraint. Target has user named constraint. -- No action taken. Source has system named constraint. Target has user named constraint. -- No action taken. Source has system named constraint. Target has user named constraint. -- No action taken. Source has system named constraint. Target has user named constraint. but previous screen was confusing me somehow ... Brg, Damir P.S. Is there any option to put in sync script SET VERIFY OFF; as well automatically? ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [October 8, 2013, 9:46am UTC](https://forums.toadworld.com/t/compare-schema-improvement-suggestion/27025/4 "2013-10-08T09:46:15Z")
Oh, well that’s not what you showed me before. J There is a difference in this case – one is named and the other is unnamed. You don’t want Toad to “un-name” the constraint in the sync script, do you? I suppose I could add an option to completely ignore these types of differences. **From:** damir.vadas\_531 [[mailto:bounce-damirvadas\_531@toadworld.com](mailto:bounce-damirvadas_531@toadworld.com)] **Sent:** Tuesday, October 08, 2013 11:36 AM **To:** [toadoraclebeta@toadworld.com](mailto:toadoraclebeta@toadworld.com) **Subject:** RE: [Toad for Oracle - Beta Discussion Forum] Compare schema improvement (suggestion) **[RE: Compare schema improvement (suggestion)](http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/p/20469/41708.aspx#41708)** Reply by damir.vadas\_531 SQL\> grant connect, resource to u1 identified by u1; Grant succeeded. SQL\> grant connect, resource to u2 identified by u2; Grant succeeded. SQL\> create table u1.t1 ( 2 COL1 NUMBER NOT NULL, 3 COL2 VARCHAR2(10 BYTE) NOT NULL 4 ); Table created. SQL\> create table u2.t1 ( 2 COL1 NUMBER constraint t1\_col1\_nn NOT NULL, 3 COL2 VARCHAR2(10 BYTE) constraint t1\_col2\_nn NOT NULL 4 ); Table created. * * * Now in toad: [![](http://www.toadworld.com/resized-image.ashx/ __size/550x0/__ key/communityserver-discussions-components-files/86/differ.png)](http://www.toadworld.com/cfs-file.ashx/__key/communityserver-discussions-components-files/86/differ.png) Now I saw that resulting script sync is empty: * * * – Play this script in U2@XE to make it look like U1@XE – – Please review the script before using it to make sure it won’t – cause any unacceptable data loss. – – U2@XE Schema Extracted by User TOOLS – U1@XE Schema Extracted by User TOOLS ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [October 8, 2013, 9:50am UTC](https://forums.toadworld.com/t/compare-schema-improvement-suggestion/27025/5 "2013-10-08T09:50:08Z")
John, I said in mine first post: _The problem is that Toad compare constraints by **name and not constrains by type and columns** (what is in fact most important). Similar thing happened with automatic pk constraint which generate unique index by oracle name._ Glad that now is all clear. 🙂 But please could you add “SET VERIFY OFF” automatically (or at least as an option … in sync scripts??? Brg, Damir ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [October 8, 2013, 9:59am UTC](https://forums.toadworld.com/t/compare-schema-improvement-suggestion/27025/6 "2013-10-08T09:59:15Z")
But it does compare constraints by type and columns. If neither constraint is named, Toad finds them and compares them and when the constraint is the same, then no difference is reported. In the 2nd example, one is user-named and the other is system named, which is significant to some people. Are you asking for an option for these differences to be ignored? About “Set verify off” – on the “misc options” tab, there is an option to include “set define off”. It’s a different option, but I think it will give the same effect you are after. **From:** damir.vadas\_531 [[mailto:bounce-damirvadas\_531@toadworld.com](mailto:bounce-damirvadas_531@toadworld.com)] **Sent:** Tuesday, October 08, 2013 11:50 AM **To:** [toadoraclebeta@toadworld.com](mailto:toadoraclebeta@toadworld.com) **Subject:** RE: [Toad for Oracle - Beta Discussion Forum] Compare schema improvement (suggestion) **[RE: Compare schema improvement (suggestion)](http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/p/20469/41711.aspx#41711)** Reply by damir.vadas\_531 John, I said in mine first post: _The problem is that Toad compare constraints by__ **name and not constrains by type and columns** _ \* (what is in fact most important). Similar thing happened with automatic pk constraint which generate unique index by oracle name.\* Glad that now is all clear. 🙂 But please could you add “SET VERIFY OFF” automatically (or at least as an option … in sync scripts??? Brg, Damir To reply, please reply-all to this email. [Stop receiving emails](http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/t/20469.aspx?subscribe=false) on this subject. Or [ Unsubscribe from Toad for Oracle - Beta](http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86.aspx?subscribe=false) notifications altogether. Toad for Oracle - Beta Discussion Forum [Flag](http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/f/86/p/20469/41711.aspx?AbuseContentId=fce2df5a-5a49-4ce3-9684-cff481c1e393&AbuseContentTypeId=f586769b-0822-468a-b7f3-a94d480ed9b0&AbuseFlag=true) this post as spam/abuse. ![](http://gyk.r.mailjet.com/fee2a479/57O1wp.gif) ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [October 8, 2013, 10:26am UTC](https://forums.toadworld.com/t/compare-schema-improvement-suggestion/27025/7 "2013-10-08T10:26:13Z")
_Are you asking for an option for these differences to be ignored?_ At least have an option for ignorance. And answer is yes I’d like to have difference as ignored. _It’s a different option, but I think it will give the same effect you are after._ Frankly it is not … especially if you join scripts in one bigger script and this bigger has some sql variables, like it is mine case. 🙂