| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: List all the constraint of a database
I have this error when I use your script :
from sys.dba_constraints a , sys.dba_constraints b
*
Mark D Powell wrote:
>
>
> 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.
-- Posted via CNET Help.com http://www.help.com/Received on Wed Nov 29 2000 - 11:30:15 CST
 |
 |