Find PostgreSQL tables that have columns which match a certain regex.

find-postgres-tables-by-column.sql

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 ~ 'regex'
  AND t.table_type = 'BASE TABLE'
ORDER BY
  t.table_schema ASC,
  t.table_name ASC;