Predictive Hacks

How to search all Tables for a column name in PostgreSQL

serach column in SQL

We’ve all been in the situation of having a chaotic database and trying to find the data that we will need in it. There is a very useful script that allows us to search for a column name in all the tables of our database.

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


table_schema       table_name 
test_database	   sample_table 

From the results, you now know that in the table “sample_table” there is a column named “column_name”.
You can even use like:

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

table_schema       table_name 
test_database	   sample_table1
test_database	   sample_table2 
serach column in postgress

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email

Leave a Comment

Subscribe To Our Newsletter

Get updates and learn from the best

More To Explore

ai copywriting
Miscellaneous

How AI Transforms Copywriting

Artificial intelligence can be used to empower human copywriters to deliver results. What if marketers could leverage artificial intelligence for