topic Re: Foreign Keys Table! in Operating System - HP-UX

Foreign Keys Table!

Pinki Meggi — Tue, 01 Aug 2006 11:56:11 GMT

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

Re: Foreign Keys Table!

spex — Tue, 01 Aug 2006 12:32:33 GMT

Hi Pinki,

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

PCS

Re: Foreign Keys Table!

Yogeeraj_1 — Wed, 02 Aug 2006 00:04:00 GMT

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

Re: Foreign Keys Table!

Pinki Meggi — Wed, 02 Aug 2006 08:18:07 GMT

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

Re: Foreign Keys Table!

Peter Godron — Wed, 02 Aug 2006 09:04:40 GMT

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

Re: Foreign Keys Table!

Eric Antunes — Wed, 02 Aug 2006 09:16:45 GMT

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

Re: Foreign Keys Table!

Peter Godron — Wed, 02 Aug 2006 09:39:13 GMT

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.

Re: Foreign Keys Table!

Yogeeraj_1 — Wed, 02 Aug 2006 23:19:32 GMT

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

Re: Foreign Keys Table!

Pinki Meggi — Thu, 03 Aug 2006 04:10:20 GMT

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