PostgreSQL: Search all tables of a database for a field called LIKE *active*

Question

In my public schema I have 1200 tables. Somewhere in one or more of this tables there are some fields called LIKE "active" like: - status_active - hr_active - who_knows_what_active_could_be

I want to find them all using PGAdmin in the console or via the normal client on console how could I do this with quick with less resources?

Answer 1

Try:

SELECT * 
FROM information_schema.columns 
WHERE TRUE
AND table_schema = 'public'
AND column_name ~* 'active'

Answer 2

You can try:

SELECT table_name,tables.table_catalog,tables.table_schema,column_name
FROM information_schema.columns
inner join information_schema.tables
using(table_name,table_schema)
WHERE  table_type = 'BASE TABLE'
  and column_name ilike '%active%'

Answer 3

select * from INFORMATION_SCHEMA.COLUMNS 
where TABLE_SCHEMA = 'your schema name'
--and TABLE_NAME ilike '%your keyword%' --when you need to search for a TABLE
and COLUMN_NAME ilike '%your keyword%' --when you need to search for a COLUMN