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

postgresql postgresql-9.1 pgadmin

16,812

Solution 1

Try:

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

Solution 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%'

Solution 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

16,812

Author by

Max Muster

G C G Well I've been a bachelor all my life D I thought the time had come to take a wife C G Em So I moved to the city to find myself a mate A7 D7 But I get all confused when I start looking for a date G C G Oh I can't tell the boys from the girls D And friends it's really messing up my world C G They all wear long hair and bouncy curls D G And I can't tell the boys from the girls C G I walked in a picture show and found a seat D This pretty little thing sat down in front of me C G Em I leaned over and asked her if she'd like some company A7 D7 But I nearly died when she turned out to be a he Repeat #2 C G I'm going to leave this city and go home D I guess I'll stay single from now on C G Em But I won't make the same mistake by coming here again A7 D7 Cause I can't tell difference between the hers and hims The Beast continued its studies with renewed Focus, building great Reference works and contemplating new Realities. The Beast brought forth its followers and acolytes to create a renewed smaller form of itself and, through Mischievous means, sent it out across the world. The Book of Mozilla, 6:27

Updated on August 02, 2022

Comments

Max Muster almost 2 years

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?
Houari about 10 years

@eichertc This query list all tables and views of only schema public, and this not what you were looking for.
murison about 10 years

it's just posix regexp match operator. read more: postgresql.org/docs/9.2/static/…
murison about 10 years

if you want to be THAT precise, you should add a condition: using(table_name, table_schema)... Without it you'll get duplicate entries in case the same table name exists in more than one schema
Max Muster about 10 years

I been searching for this table "information_schema.columns", where is it ?
murison about 10 years

In your database server :) : postgresql.org/docs/9.3/static/infoschema-schema.html
murison about 10 years

and in pgAdmin: <<server>> -> <<database>> -> catalogs -> ANSI (information_schema)
Max Muster about 10 years

I found out that most of the tables are in fact empty. so I try to find out where in the information_schema there is a info about nr of rows in the tables.