Breaking News: Grepper is joining You.com. Read the official announcement!

oracle list foreign keys referencing a table

Famous Flatworm answered on April 11, 2024 Popularity 1/10 Helpfulness 1/10

answer oracle list foreign keys referencing a table

oracle list foreign keys referencing a table

Comment

To find all foreign keys referencing a target table, join together two copies of the all_constraints table, one for the primary key constraint on the target table (here labelled "pk") and one for the foreign key constraints on other tables referencing that pk constraint (here labelled "fk"), and then join the all_cons_columns table for extra column name info.

select
	cols.owner,
	cols.table_name,
    cols.column_name,
    fk.constraint_name,
    fk.r_owner,
    pk.table_name as pk_table_name,
    pk.constraint_name as pk_constraint_name
from all_constraints pk
join all_constraints fk on fk.r_owner = pk.owner
    and fk.r_constraint_name = pk.constraint_name
join all_cons_columns cols on cols.owner = fk.owner
	and cols.constraint_name = fk.constraint_name
where pk.table_name= 'TARGET TABLE NAME HERE'
    and fk.constraint_type = 'R';

Popularity 1/10 Helpfulness 1/10 Language sql

Source: Grepper

Tags: foreign-keys list oracle sql

Link to this answer
Share Copy Link

Contributed on Apr 11 2024

Famous Flatworm

0 Answers Avg Quality 2/10

Continue with Google

By continuing, I agree that I have read and agree to Greppers's Terms of Service and Privacy Policy.

oracle list foreign keys referencing a table

Contents

More Related Answers

oracle list foreign keys referencing a table

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.