Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Foreign Keys Table!

SOLVED
Go to solution
Pinki Meggi
Frequent Advisor

Foreign Keys Table!

Hi everyone,

im working with Orcale9i and im having a little problem.
i need to know all the tables which have a foreign key to an table. I find out a table with all tables, comments, columns, columns_comments ( all_tables, all_tab_comments, all_col_comments), i also find a table withh all constraints but there is no informatio about the table that constraint is refered. I need to know if there is a table with this information? Can anyone help me or guide me to find out this information, please? i will be very greatfull

Thanks in advance

Best Regards

Pinki
8 REPLIES
spex
Honored Contributor

Re: Foreign Keys Table!

Hi Pinki,

SELECT DISTINCT table_name
FROM sys.dba_constraints
WHERE constraint_name LIKE 'FK_%';

PCS
Yogeeraj_1
Honored Contributor

Re: Foreign Keys Table!

hi Pinki,

unless you are systematic in naming your foreigh key constraints as 'FK_xxx' the above query might not work.

if you only want to know the table names, the following will do it:
select table_name
from all_constraints
where constraint_name in (select R_CONSTRAINT_NAME
from all_constraints)

The following data dictionary views may also be of help:
all_constraints
all_cons_columns

if fact can be any of (all_, user_ or dba_)

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Pinki Meggi
Frequent Advisor

Re: Foreign Keys Table!

Hi

Thanks for your attention and response..


Well i think i didnt explain well what i need.

I dont want to know foreign keys of one table. What i need is, for example: I have three tablea: Employee, Department and Institution. Employee and Department have foreign keys to Institution. I need a script that finds all the tables which have a foreign key that point to Institution.

I hope you understand....



Best Regards

Pinki
Peter Godron
Honored Contributor

Re: Foreign Keys Table!

Hi,
something along the lines as produced by TOAD when clicking on the 'used by' tab?
Eric Antunes
Honored Contributor

Re: Foreign Keys Table!

Hi Pinki,

I think this is what you want. It searchs all tables in an owner that is referenced in another table:

select dc.table_name, dc.owner
from dba_constraints dc
where dc.constraint_type = 'R'
and exists (select 1 from dba_constraints dc2
where dc2.constraint_name = dc.r_constraint_name
and dc2.constraint_type = 'P')
and dc.owner =

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Peter Godron
Honored Contributor

Re: Foreign Keys Table!

Pinki,
I think:
select a.table_name, b.table_name
from all_constraints a, all_constraints b
where a.constraint_name = b.R_CONSTRAINT_NAME
and a.owner=''
order by 1,2;

should work.
It shows the table and any table that reference it.
Yogeeraj_1
Honored Contributor
Solution

Re: Foreign Keys Table!

hi pinki,

to add to above reply, here is the SQL that you should run:
select a.table_name, b.table_name"is being referenced by"
from all_constraints a, all_constraints b
where a.constraint_name = b.R_CONSTRAINT_NAME
and a.owner='&Table_Owner'
and a.table_name='INSTITUTION'

You will be prompted to enter the owner of the table INSTITUTION

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Pinki Meggi
Frequent Advisor

Re: Foreign Keys Table!

Hi,

Thanks a lot. It works perfectly:

select a.table_name, b.table_name"
from all_constraints a, all_constraints b
where a.constraint_name=b.R_CONSTRAINT_NAME
and a.owner='&Table_Owner'
and a.table_name='INSTITUTION';

Thanks once again.


Best Regards!

Pinki