Find tables where column exists
To find all the tables where a specific names column exists, the query below can be used.
SELECT t.table_schema, t.table_name, c.column_name
FROM information_schema.tables t
INNER JOIN information_schema.columns c ON c.table_name = t.table_name AND c.table_schema = t.table_schema
WHERE c.column_name LIKE <COLUMN_NAME>
AND t.table_schema not in ('information_schema', 'pg_catalog')
AND t.table_type = 'BASE TABLE'
GROUP BY t.table_schema, t.table_name
Also one could aggregate the tables containing the column into a single result where the table names is separated by a space or comma. This can be usefull when further processing is done by an another script engine.
string_agg(t.table_name, ' ')Find tables that has table name with prefix
SELECT t.table_schema,
t.table_name
FROM information_schema.tables t WHERE t.table_schema = 'public' AND t.table_name NOT LIKE '<PREFIX_>%';