Re: List all the constraint of a database

From: Mark D Powell <markp7832_at_my-deja.com>
Date: Wed, 29 Nov 2000 15:06:45 GMT
Message-ID: <90361v$va8$1@nnrp1.deja.com>

In article <t2a4nctc8lrp37_at_corp.supernews.com>, rodrigues <rodr_ric_at_yahoo.com> wrote:
>
>
> I would like to list all the referential intergrity attach to a table.
>
> I know I can do this to list them :
> select table_name from all_constraints
> where R_constraint_name like 'name of the constarint'
>
> But I want to have also the listing of refenrential (indirect : made
> thought an other table :table1-table2-table3)
>
> Anyone to help me
>
> ROdrigues Richard
>
> --

That's a pretty simple query to write:

UT1> @cons_fk_to_tbl
Enter table name to show referential constraints on => gl_acct Referenced Table Referencing Table Rule Status

------------------------------ ------------------------------ ---------
 --------
Referenced Constraint          Foreign Key Name
------------------------------ ------------------------------
GL_ACCT                        AC_DEPT_ACCT                   CASCADE
ENABLED
GL_ACCT_PK                     FK_AC_IC_DEPT_GL_ACCT
GL_ACCT                        AC_WC_LABOR_MATRIX_ACCT        CASCADE
ENABLED
GL_ACCT_PK                     FK_AC_WC_LABOR_ACCT_GL_ACCT

set echo off
rem
rem SQL*Plus script to locate foreign keys for a specific table rem
rem 1996 07 16 m d powell New script rem 1998 06 23 m d powell Modify for general use; add more columns rem
rem remove 'table_name like' line and all constraints show up rem
set verify off
column constraint_name format a30
column r_constraint_name format a30
column table_name format a30
accept tbl_nm prompt "Enter table name to show referential constraints on => "

select

       b.table_name        "Referenced Table"     ,
       a.table_name        "Referencing Table"    ,
       a.delete_rule       "Rule"                 ,
       a.status            "Status"               ,
       a.r_constraint_name "Referenced Constraint",
       a.constraint_name   "Foreign Key Name"

from sys.dba_constraints a , sys.dba_constraints b where b.table_name like upper('&tbl_nm'||'%') and a.r_constraint_name = b.constraint_name /
undefine tbl_nm

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Nov 29 2000 - 09:06:45 CST