Postgresql How to get all tables related to another table?
Suppose I have a supplier table with supplierID
and 14 more columns. I want to get a list of all tables that have a column from a vendor.
I have the following query, it gets all tables containing FK from supplier.supplierID
:
select (select r.relname from pg_class r where r.oid = c.conrelid) as table,
(select r.relkind from pg_class r where r.oid = c.conrelid) as type,
(select array_agg(attname) from pg_attribute
where attrelid = c.conrelid and ARRAY[attnum] <@ c.conkey) as col,
(select r.relname from pg_class r where r.oid = c.confrelid) as ftable,
(select n.nspname from pg_class r left join pg_namespace n on r.oid=n.oid where r.oid = n.oid) as schema
from pg_constraint c
where c.confrelid = (select oid from pg_class where relname = 'supplier') and
c.confkey @> (select array_agg(attnum) from pg_attribute
where attname = 'supplierID ' and attrelid = c.confrelid);
This is incomplete because I need to know about all the columns of the provider, any of them could be FK elsewhere (let all columns be unique).
Is there a way to improve my query? I don't like running 15 items across all columns.
source to share
Can you check the below query and see if it gives the desired output
SELECT mytable.table_name AS mytablename,
kcu.column_name AS mycolumns,
allforeignkey.table_name AS referredtable,
allforeignkey.column_name AS referredcolumn
FROM information_schema.table_constraints AS mytable
JOIN (
SELECT table_name,
column_name,
unique_constraint_name
FROM information_schema.referential_constraints
INNER JOIN information_schema.key_column_usage AS kcu
ON information_schema.referential_constraints.constraint_name = kcu.constraint_name
) AS allforeignkey
ON mytable.constraint_name = allforeignkey.unique_constraint_name
INNER JOIN information_schema.key_column_usage AS kcu
ON mytable.constraint_name = kcu.constraint_name
WHERE mytable.constraint_type = 'UNIQUE'
AND mytable.table_name = 'your_table_name';
Basically what I do is that I take all the columns of the UNIQUE
specified table, then list all the foreign keys in the database, and concatenate the results, which will give the columns UNIQUE
that are referenced in other tables
source to share