Query to find foreign keys in an Oracle database

by Cliff 30. November 2012

The following query will return all of the columns for a table and will include an indicator if the column is a foreign key:

 

select utc.*,
case when exists(
select 1 
from all_cons_columns ucc
join all_constraints uc on ucc.constraint_name = uc.constraint_name
where uc.constraint_type = 'R'
and uc.table_name = utc.table_name
and ucc.column_name = utc.column_name)
then 1
else 0
end IsForeign
from all_tab_columns utc where utc.table_name = 'YOURTABLE' and utc.owner = 'THEOWNER'

 

Tags: oracle

Oracle

Related posts

Comments are closed

Cliff Gray's Info

Cliff Gray
Developer/Founder GrayTechnology.com.

E-mail me Send mail

Authors

Calendar

<<  January 2021  >>
MoTuWeThFrSaSu
28293031123
45678910
11121314151617
18192021222324
25262728293031
1234567

View posts in large calendar

Download BlogEngine.NET

Download at CodePlex

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2021

Subscribe