Re: Connection Constraint - Index

From: Ben Ryan <benryan_at_my-deja.com>
Date: Sun, 20 Feb 2000 00:46:00 GMT
Message-ID: <88ndg8$frd$1@nnrp1.deja.com>

In article <38AE738E.15A9C381_at_0800-einwahl.de>,   Martin.Haltmayer_at_0800-einwahl.de wrote:
> And what is even more amazing: in my Oracle 8.1.5 on NT 4.0 it is the
> second one.
>
> So there is a real need for finding out where the connection between
> the constraint and the index is recorded and retrievable for
> programmers and DBAs.
>
> Does anyone know this connection view?
>
> Martin
>

SELECT c.constraint_name,s.con#,s2.enabled,RPAD(o.object_name,20) object_name

   FROM sys.con$ s,user_constraints c, sys.cdef$ s2, user_objects o WHERE c.constraint_type IN ('P','U')

   AND c.table_name = replace_with_name_of_table
   AND c.constraint_name = s.name
   AND s.con# = s2.con#

   AND s2.enabled = o.object_id;

Which, as far as I can tell, works on both 7.3.4.4 and 8.0.5.

For more info., see thread 'Joining constraints to indexes in the system dictionary (Oracle8)' fourth message, which is me replying to info. sent by Thomas Kyte of Oracle.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Feb 19 2000 - 18:46:00 CST