Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Sqlplus Script to find FKs

Sqlplus Script to find FKs

From: <argosy22_at_my-deja.com>
Date: Fri, 17 Dec 1999 17:18:14 GMT
Message-ID: <83dr8i$o9$1@nnrp1.deja.com>


HI all,

Here's a script that I thought I would share with you. It will find all the foreign keys, for one table. It wasn't immediately obvious to me from looking at the repository.

accept ls_table_name prompt "Enter a table to find the foreign keys for: " ;

select 	c1.constraint_name,
	c1.table_name		First_table,
	c1.constraint_type	TYPE,
	c2.table_name 		referred_table,
	c2.constraint_type	TYPE
from 	all_constraints c1,
	all_constraints c2
where 	c1.r_constraint_name	= c2.constraint_name
and	c1.table_name 		= UPPER(LTRIM(RTRIM( '&ls_table_name'
)))

/* and c1.owner = c2.owner */

To find all FK in a database:

select 	c1.constraint_name,
	c1.table_name		First_table,
	c1.constraint_type	TYPE,
	c2.table_name 		referred_table,
	c2.constraint_type	TYPE
from 	all_constraints c1,
	all_constraints c2
where 	c1.r_constraint_name	= c2.constraint_name

/* and c1.owner = c2.owner */

Use them if you like.

Argosy

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 17 1999 - 11:18:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US