Foreign Keys Table!

[Pinki Meggi]

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

[spex]

Hi Pinki,

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

PCS

[Yogeeraj_1]

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

[Pinki Meggi]

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]

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

[Eric Antunes]

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

[Peter Godron]

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 · Accepted solution]

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

[Pinki Meggi]

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