Re: List all the constraint of a database

In article <t2af975vt20f26_at_corp.supernews.com>,   rodrigues <rodr_ric_at_yahoo.com> wrote:
> I have this error when I use your script :
>
> from sys.dba_constraints a , sys.dba_constraints b
> *
> ERREUR à la ligne 8:
> ORA-00942: table or view does not exist
>
> 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 --
> >
>
> --
>

The script as written is intended for DBA's. Substitute all_constraints for sys.dba_constraints for non-dba use.

--
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.