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@(protected)
[mailto:oracle-l-bounce@(protected)] 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?
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN " >
<HTML > <HEAD > <TITLE > </TITLE >
<META http-equiv=Content-Type content= "text/html; charset=us-ascii " >
<META content= "MSHTML 6.00.2800.1400 " name=GENERATOR > </HEAD >
<BODY >
<DIV dir=ltr align=left > <SPAN class=564352722-09062004 >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. </SPAN > </DIV >
<DIV dir=ltr align=left > <SPAN class=564352722-09062004 > </SPAN > </DIV >
<DIV dir=ltr align=left > <SPAN class=564352722-09062004 > </SPAN > </DIV >
<DIV dir=ltr align=left > <SPAN class=564352722-09062004 > <FONT face= "Courier New "
size=2 >select <BR > c.username || '. ' || d.table_name || '. ' ||
d.column_name as constraint_column, <BR > b.name || ' (NN) ' as
nn_constraint_name, <BR >-- * you don 't really need this text since we
know it will be <BR >-- * "COLUMN_NAME " IS NOT NULL <BR >--
* a.condition as nn_constraint_text, <BR > f.name || ' (CHECK) ' as
ck_constraint_name, <BR > e.condition as
ck_constraint_text <BR > from <BR > sys.cdef$ a, sys.con$ b,
dba_users c, dba_cons_columns d, <BR > sys.cdef$ e, sys.con$ f,
dba_cons_columns g <BR > where <BR > a.type# = 7 <BR >
and a.cols = 1 <BR > and a.con# = b.con# <BR > and b.owner#
= c.user_id <BR > and c.username = d.owner <BR > and b.name
= d.constraint_name <BR > and e.obj# = a.obj# <BR > and
e.type# = 1 <BR > and e.cols =
1 --***
<-- ---- --- forgot this line <BR > and e.con# =
f.con# <BR > and f.owner# = b.owner# <BR > and c.username =
g.owner <BR > and f.name = g.constraint_name <BR > and
d.column_name = g.column_name <BR >-- * add your specific conditions here
from dba_users <BR >-- * and dba_cons_columns <BR > and c.username
= user ; </FONT > </SPAN > </DIV >
<P > <FONT size=2 > <FONT face= "Courier New " > </FONT > <BR >-- --Original
Message-- -- <BR >From: oracle-l-bounce@(protected) [ <A
href= "mailto:oracle-l-bounce@(protected) " >mailto:oracle-l-bounce@(protected) </A >]
On Behalf Of Barbara Baker <BR > <BR >I appear to have a bit of a mess on my hands.
I 've <BR >identified some tables that have a duplicate "not <BR >null " constraint on
the same column. Only difference <BR >in the constraints is that one is
generated and one is <BR >user named (even tho they 're both
sys_c00xxx <BR >constraints). <BR > <BR >(I believe this happened when a vendor used a
3rd <BR >party pkg to try to duplicate their schema in
our <BR >database.) <BR > <BR >I 'd like to identify all of the tables with
this <BR >condition. Any method I can think to do this requires <BR >comparing the
search condition of dba_constraints, <BR >which is a LONG. <BR > <BR >Can anyone think
of a way to do this? </FONT > </P > </BODY > </HTML >
