Re: How to get a list of tables that have a particular column value?

Rajan, Pavithra wrote:
>
> Hello - I would like to know if there is a way to find all the table
> names in a data base that have a particular* column value* eg:"volt"
> .ie given a column value (not column name) how to I find which tables
> and their column names have them .Thank you.
>

Do you want to search for values 'volt' in -any- column of any table, or
just in certain columns if they exist?

you can enumerate the tables by querying information_schema.tables, and
you can enumerate the columns of a table via information_schema.columns,
so I'd imagine you would need a script or program that iterates through
the tables, and through the columns of each table, then constructs and
executes a query of that column of that table for your value. You'll
probably want to check the column datatype first and not query numeric
fields.

when you do these queries, just what is it you're looking for, the
simple existence of the value in table X as a yes/no thing? since
each table has its own structure, its unclear what other useful info you
could extract

on a large database, this is going to be very time consuming as it
likely will require sequential scanning everything multiple times if you
want to look at every text column of every table.

now, if this is a requirement to look for this value in a specific
column of various specific tables, perhaps that column should be its OWN
table, (id serial, thing text) and the other tables reference this as a
foreign key.