Postgresql How to get all tables linked to another table?

Question

Suppose I have a supplier table with supplierID and another 14 columns. I want to get a list of all tables that any column from supplier is fk there.

I have the following query, it gets all tables which contains 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 columns in supplier, any one of them can be FK somewhere else (lets suppose all columns are unique).

Is there any way to improve my query? I hate to run it 15 items over all columns.

can you look at this SO post http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys I think this is what you need. — Nandakumar V, Jun 03 '15 at 06:49
it's the opposite. this query gives for a specific table where it's FK originated from. I need it the other way around. for a specific table look in the whole DB and give me all the places any of my columns is used as FK. — avi, Jun 03 '15 at 07:08

score 3 · Accepted Answer · edited Oct 17 '19 at 10:14

Can you check the below query and see if it gives the desired result

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 am doing is that I took all UNIQUE columns of the specified table, then the list of all foreign keys in the database and join the results, which will give the UNIQUE columns that are been referenced in other tables

what mytable.constraint_type = 'PRIMARY KEY' stantands for? I only provide table name.. the query isn't about checking if a specific column is used a FK.... any column from the table can be FK somewhere else... my goal is not to run same query 15 times over 15 columns... — avi, Jun 03 '15 at 07:56

Postgresql How to get all tables linked to another table?

1 Answers1