Re: Foreign keys.

Excellent script but it won't do much good if the user isn't granted DBA. IMHO it's better to use USER_CONSTRAINTS.

In article <9hNE5.5747$mC.389522_at_monger.newsread.com>,   "Van Messner" <vmessner_at_bestweb.net> wrote:
> ACCEPT tname PROMPT 'Enter Name of Table You Want to Look at:'
> ACCEPT qowner PROMPT 'Enter Name of Table Owner:'
> column fowner heading 'Owner Of |Table With|Foreign Key' format a11
> column ftable heading ' Name Of | Table With| Foreign Key' format a16
> column fconname heading ' FK Name ' format a22
> column fdel heading 'Delete|Rule' format a10
> column fref heading 'Constraint in|Parent Table|Pointed to by FK'
 format a22
> column fstatus heading 'FK Status' format a8
> TTITLE ' FOREIGN KEYS THAT POINT TO THIS TABLE '
> SELECT
> frn.owner fowner,
> frn.table_name ftable,
> frn.constraint_name fconname,
> frn.delete_rule fdel,
> frn.r_constraint_name fref,
> frn.status fstatus
> FROM
> dba_constraints par,
> dba_constraints frn
> WHERE
> par.table_name = UPPER('&tname')
> and par.owner = UPPER('&qowner')
> and par.constraint_name = frn.r_constraint_name
> ;
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:971214678.2789.3.pluto.d4ee154e_at_news.demon.nl...
> > select r.table_name, r.constraint_name
> > from user_constraints c, user_constraints r
> > where c.table_name = '<your table>'
> > and c.constraint_type='P'
> > and r.referenced_name=c.constraint_name
> > and r.referenced_owner=c.owner
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> >
> > <vdolt_at_my-deja.com> wrote in message news:8rvob0$i50
 $1_at_nnrp1.deja.com...
> > > I am trying to delete a record from the table. Other tables have
> > > foreign keys on that table, which prevents me from deleting
 original
> > > record. Is there a query I can run to uncover all the foreign key
 on
> > > that table?
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
>

--
David Fitzjarrell
Oracle Certified DBA


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