I have a database without documentation.
There is a table which has a column language_id which is a foreign key to another table. I want to know which table that foreign key references. I don't want to find out just by looking by eye at all the tables because there are about 120 tables and the names are not descriptive unfortunately.
Is there a way to find that?
I am using SQL Developer to query a remote Oracle 11g database.
You can query the data dictionary, with something like:
select uc_r.table_name, ucc_r.column_name, uc_r.constraint_name,
uc_p.constraint_name, uc_p.table_name, ucc_p.column_name
from user_constraints uc_r
join user_cons_columns ucc_r on ucc_r.constraint_name = uc_r.constraint_name
join user_constraints uc_p on uc_p.constraint_name = uc_r.r_constraint_name
join user_cons_columns ucc_p on ucc_p.constraint_name = uc_p.constraint_name
and ucc_p.position = ucc_r.position
where uc_r.constraint_type = 'R';
which looks for all foreign key constraints (type R), finds the matching primary/unique key, and matches up the columns from both the tables. You can restrict that to specific tables, columns, or constraints of course, but a wider view might be useful if you're trying to find all your mappings.
If I create a dummy parent/child relationship, using unnamed constraints (which may be what you mean by the names not being descriptive):
create table language (id number primary key, name varchar2(10));
create table my_table (language_id references language(id));
then that query finds:
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
----------- ------------- --------------- --------------- ----------- -------------
MY_TABLE LANGUAGE_ID SYS_C00111327 SYS_C00111326 LANGUAGE ID
From SQL Developer you can also open the table viewer (from the expanded table list under your connection, in the panel on the left); the initial view shows the table columns, but if you click on the Constraints tab it will show you the same information - plus lots more about the constraint. That will only show you a single table at a time though.
You say you're using SQL Developer.
So, just open the table.
Go to the constraints page.
Find your foreign key.
Look at the R_TABLE_NAME column.
I used below query in Oracle
select a.table_name child_table, a.column_name child_column, b.table_name parent_table, b.column_name parent_column, a.position, a.constraint_name child_constraint, b.constraint_name parent_constraint from all_cons_columns a join all_constraints lc on a.owner = lc.owner and a.constraint_name = lc.constraint_name join all_constraints rc on lc.r_owner = rc.owner and lc.r_constraint_name = rc.constraint_name join all_cons_columns b on rc.owner = b.owner and rc.constraint_name = b.constraint_name and a.position = b.position where a.owner = '<>' and b.TABLE_NAME = '<>' and lc.constraint_type = 'R' order by a.constraint_name, a.table_name, a.position;
