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

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: [https://stackoverflow.com/questions/18508422/how-to-find-a-table-having-a-specific-column-in-postgresql] 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 | ex

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_namepublicemployeespubliccustomerssalesorders

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.