Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Sqlplus Script to find FKs
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