Recently I had to create some tables in Oracle and fill them with data from other tables for testing.

It is quite easy to create and modify tables, however it can be a pain digging through undocumented dependencies, table constraints which describe parent child relationships between tables.

In order to find foreign keys for a table, adjust below query by replacing 'table_name' with the name of a table of interest and execute.

Synonym 'a' is holding the child table details and synonym 'uc' is holding the parent table details.

Enjoy.

SQL

SELECT a.table_name, a.column_name, uc.table_name, uc.column_name
FROM all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner
AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
AND c.r_constraint_name = c_pk.constraint_name
JOIN USER_CONS_COLUMNS uc on uc.constraint_name = c.r_constraint_name
WHERE c.Table_Name = upper('table_name')
OR uc.table_name = upper('table_name');