Foriegn key reference table in oracle

[vishnunekkanti]

Hello

Can any one help me how I can find a table that has foreign key references from other table?

Thanks in advance


Vishnu

 

[mikesgroovin]

There are a few ways to view them but it would help knowing a little more about what you want to accomplish. Given your post, to list all of them just do a statement that lists all the records from SYS.ALL_CONSTRAINTS and SYS.ALL_CONS_COLUMNS. You are going to have duplicate records though if you don't join the tables. I don't have access to a DB in front of me, so the exact names of the columns that you need to join escape me. But, if you look at the tables, the columns you need to join in order to not get dup record results will be pretty clear. I think "owner" is one of them... lol i don't know. It's pretty obvious if you do a "select *" though from both tables independently. Or just view the table structures and the names of the columns should be similar if not exactly the same.

 

[Suman Gadikrindi]

Hello

Can any one help me how I can find a table that has foreign key references from other table?

Thanks in advance


Vishnu

Enjoy with the following statement :happy: :

select acc.table_name REFERENCING_TABLE_NAME, acc.column_name REFERENCING_COLUMN_NAME
from all_constraints ac1,
all_constraints ac2,
all_cons_columns acc
where ac1.constraint_type = 'P'
and ac1.table_name = :table_name
and ac2.r_constraint_name = ac1.constraint_name
and ac2.constraint_name = acc.constraint_name;

Regards,
Suman Gadikrindi
Logica