Get all of the tables linked by a Foreign Key

No Comments

Here is a handy little PostgreSQL query that will list all of the tables linked by a foreign key to the table YOUR_TABLE.
The output will include the keys on each side.
This can be very useful if you want to build up a map of your database through the relationships between the tables.

select confrelid::regclass, af.attname as fcol, conrelid::regclass, a.attname as col
from pg_attribute af,
pg_attribute a,
(select conrelid,confrelid,conkey[i] as conkey,
confkey[i] as confkey from (select conrelid,confrelid,conkey,confkey, generate_series(1,array_upper(conkey,1)) as i from pg_constraint where contype = ‘f’) ss) ss2
where af.attnum = confkey and af.attrelid = confrelid and a.attnum = conkey and a.attrelid = conrelid AND (conrelid::regclass = ‘YOUR_TABLE’::regclass);

Categories: PostgreSQL

Leave a Reply

Your email address will not be published. Required fields are marked *