How to get a list of all tables used in a specific column in Postgresql

Question

I have a table users : id,name, etc...

I want to get a list of all tables,views that use users.id

for example if table user_new is refrencing users.id as FK I want it to be desplayed in the query.

How do I do that?

Answer 1

Use pg_depend.

Example:

create table some_table (
    id serial primary key);

create table child_table (
    id int, 
    master_id int references some_table(id));

create view some_view as 
    select * from some_table;

select distinct pg_describe_object(classid, objid, objsubid)
from pg_depend 
where refobjid = 'some_table'::regclass

                     pg_describe_object
------------------------------------------------------------
 sequence some_table_id_seq
 constraint some_table_pkey on table some_table
 type some_table
 default for table some_table column id
 constraint child_table_master_id_fkey on table child_table
 rule _RETURN on view some_view
(6 rows)

The above query selects all objects referencing to some_table. You can filter the result for a specific column and/or desired type of relationship. To select only tables and views depending on the first column, use:

select distinct pg_describe_object(classid, objid, objsubid)
from pg_depend
where 
    refobjid = 'some_table'::regclass 
    and refobjsubid = 1 -- only for column #1
    and deptype = 'n'; -- a normal relationship between separately-created objects

                     pg_describe_object
------------------------------------------------------------
 constraint child_table_master_id_fkey on table child_table
 rule _RETURN on view some_view
(2 rows)    

Answer 2

 SELECT conrelid::regclass table_name
    FROM pg_constraint c
    WHERE c.confrelid = 'user'::regclass::oid
        AND c.confkey @> (
            SELECT array_agg(attnum)
            FROM pg_attribute
            WHERE attname = 'id'
                AND attrelid = c.confrelid
            )
        AND contype = 'f'

---

As per PostgreSQL Doc

pg_constraint :

The catalog pg_constraint stores check, primary key, unique, foreign key, and exclusion constraints on tables

contype : - c = check constraint, f = foreign key constraint, p = primary key constraint, u = unique constraint, t = constraint trigger, x = exclusion constraint,
conrelid : The table this constraint is on,
confrelid : If a foreign key, the referenced table,
conkey : If a table constraint (including foreign keys, but not constraint triggers), list of the constrained columns

Refer Object Identifier Types for oid and regclass

Answer 3

try to use information_schema

for views:

select table_schema,table_name from information_schema.views where view_definition ilike '%users.id%';

for tables:

select table_schema,table_name from information_schema.columns where table_name='users' and column_name='id';