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'