Find tables with specific column name in PostgreSQL database

Query below finds all tables that have 'last_name' column.

See also tables that don't have a column with specific name.


select t.table_schema,
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;


  • table_schema - name of schema of found table
  • table_name - name of found table


  • One row represents a table
  • Scope of rows: all found tables
  • Ordered by table schema

Sample results

List of tables that have 'last_name' column.

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.