How to search tables for a specific column name in PostgreSQL?

How to search tables for a specific column name in PostgreSQL?
Photo by Caspar Camille Rubin / Unsplash

Let's say you have a massive database with many tables and columns.

You want to search for a specific column name but don't know where to start.
No worries.

Here's a simple way you can do so:

select table_name from information_schema.columns where column_name = 'your_column_name'

An example with orders table and order_id column name:

| table_name  |
| ----------- |
| orders      |
example

If you also want more information such as the table schema, here's another script:

select t.table_schema,
       t.table_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 = 'last_name'
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
order by t.table_schema;

An example:

table_schematable_name
publicemployees
publiccustomers
salesorders

How about finding tables with standard columns?

select t.table_schema,
       t.table_name
from information_schema.tables t
left join (select table_schema, table_name
           from information_schema.columns
           where column_name = 'last_update') c
                    on c.table_name = t.table_name 
                    and c.table_schema = t.table_schema
where c.table_name is null 
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
order by t.table_schema, 
         t.table_name;

Standard columns are helpful for a variety of reasons such as knowing all the user-defined tables or keeping track of transactions.