Re: NEED SCRIPT: list all constraints

From: Jomarlen <jomarlen_at_aol.com>
Date: 1997/10/02
Message-ID: <19971002205601.QAA29711@ladder02.news.aol.com>#1/1

You'll find the same information in the view ALL_CONSTRAINTS, plus some more detail as in CONSTRAINT_DEFS.

Visit my web site http://members.aol.com/jomarlen and, on my Oracle Utilities page you'll find the ddl for a view ALL_FK_CONSTRAINTS

SQL> desc all_fk_constraints

 Name                            Null?    Type
 ------------------------------- -------- ----
 CONSTRAINT_NAME                 NOT NULL VARCHAR2(30)
 PARENT_TABLE_OWNER              NOT NULL NUMBER
 PARENT_TABLE                    NOT NULL VARCHAR2(30)
 PARENT_COLUMN                   NOT NULL VARCHAR2(30)
 CHILD_TABLE_OWNER               NOT NULL NUMBER
 CHILD_TABLE                     NOT NULL VARCHAR2(30)
 CHILD_COLUMN                    NOT NULL VARCHAR2(30)
 CASCADE_DELETE                           VARCHAR2(3)
 CHILD_TABLE_ID                  NOT NULL NUMBER
 CHILD_COLUMN_ID                 NOT NULL NUMBER

This will tell you about your foreign keys which I guess is what you are interested in.

The one drawback is that the table owners are shown as the user#. This is because this view is used in forms to enforce RI on the client side (see article about this on the same page) and the 2 additional joins needed to resolve username from user# kill performance stone dead.

Hope this helps
John

John C. Lennon
Utility Partners Inc.
4300 West Tropicana Blvd LVO-UPL
Las Vegas NV 89103

FOR UTILITY PARTNERS EMPLOYMENT
OPPORTUNITIES VISIT www.utilpart.com

e-mail: jomarlen_at_aol.com
Web Site: http://members.aol.com/jomarlen/

The views expressed in this message
are those of the author and not
necessarily those of U.P. Inc.
and/or its employees.

>Does anyone out there know of a good set of scripts to display information
>about
>a set of tables, in particular the constraints? My database was constructed
>with
>foreign key and other constraints which are not documented (at least, not in
>a
>way which is accessible to me), and trying to create test data is a problem
>if
>you don't satisfy the referential integrity. Oh, and for some reason the view
>CONSTRAINT_DEFS, alluded to in the Oracle dox, is missing. Is that one of
>those
>that has to be created by the DBA?
>
>
>
>
>
>
>
Received on Thu Oct 02 1997 - 00:00:00 CDT