CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Lex
Lex

Reputation: 396

Finding reference table from foreign key in SQL Developer

OK so I'm new to SQL and not very familiar with Oracle SQLDev, but the tool that I'm making requires that I access an Oracle database and gather some information. I'm trying to figure what table a foreign key is pointing to.

This database has thousands of tables.

Example:

I got a table (TASKS) that contains the following columns [id, user, task_type, task_group]. The problem is that all of this values are ids which correspond to another table, and the table naming convention is not intuitive.

So how can I find out what table task_type is a pointing to?

Upvotes: 1

Views: 5816

Answers (3)

junaling
junaling

Reputation: 1527

see my post here (2nd answer) as to how you can add this as an extension in sqldeveloper:

How can I find which tables reference a given table in Oracle SQL Developer?

Upvotes: 1

user3733460
user3733460

Reputation: 1

select table_name, constraint_name, status, owner
from all_constraints
where r_owner = :r_owner
and constraint_type = 'R'
and r_constraint_name in
 (
   select constraint_name from all_constraints
   where constraint_type in ('P', 'U')
   and table_name = :r_table_name
   and owner = :r_owner
 )
order by table_name, constraint_name

Upvotes: -1

DonCallisto
DonCallisto

Reputation: 29912

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;

that should work

Upvotes: 4

Related Questions