RE: Help with sql identifying dup constraints

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 9 Jun 2004 15:30:15 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F878D6@irvmbxw02>

Whoops! That suggested query I sent had an error, I wasn't checking that the check constraint (possibly duplicating a not null constraint) only had one column. Please try this.    

select

   c.username || '.' || d.table_name || '.' || d.column_name as constraint_column,

   b.name || ' (NN)' as nn_constraint_name,
-- * you don't really need this text since we know it will be

• * "COLUMN_NAME" IS NOT NULL
• * a.condition as nn_constraint_text, f.name || ' (CHECK)' as ck_constraint_name, e.condition as ck_constraint_text from sys.cdef$ a, sys.con$ b, dba_users c, dba_cons_columns d, sys.cdef$ e, sys.con$ f, dba_cons_columns g where a.type# = 7 and a.cols = 1 and a.con# = b.con# and b.owner# = c.user_id and c.username = d.owner and b.name = d.constraint_name and e.obj# = a.obj# and e.type# = 1 and e.cols = 1 --*** <----------- forgot this line and e.con# = f.con# and f.owner# = b.owner# and c.username = g.owner and f.name = g.constraint_name and d.column_name = g.column_name
• * add your specific conditions here from dba_users
• * and dba_cons_columns and c.username = user ;

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Barbara Baker

I appear to have a bit of a mess on my hands. I've identified some tables that have a duplicate "not null" constraint on the same column. Only difference in the constraints is that one is generated and one is user named (even tho they're both sys_c00xxx constraints).

(I believe this happened when a vendor used a 3rd party pkg to try to duplicate their schema in our database.)

I'd like to identify all of the tables with this condition. Any method I can think to do this requires comparing the search condition of dba_constraints, which is a LONG.

Can anyone think of a way to do this?

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

Received on Wed Jun 09 2004 - 17:27:09 CDT