Hello -Yes I need to find out the column value like '%Volt%' in any column of data_type (character varying) of any table. Basically what I need to do is go thro each columns of all tables and find any entries that have Voltage followed by some number e.g. 'Voltage 4.8000'.Then I need to use regexep_replace function to curtail the precision to two digits after decimal instead of 4. Eg:table name 'app' has a column name description which has 4 entries like | description | | character varying(50) | | | | Voltage 2.4000 | | Voltage 4.8000 | | Voltgae 3.0509 | | | Voltage 1.0010 | Then I run a sql file with this command any many other Update commands form other tables that have similar entries in various columns. UPDATE app SET app = regexp_replace(description,'4.8000','4.80') where description like 'Volt%'; Hence I need to know all the tables and their column name ("data_type :character varying") that has this 4 digit extn. Thank you. -----Original Message----- From: John R Pierce [mailto:pierce@xxxxxxxxxxxx] Sent: Wednesday, December 23, 2009 3:11 PM To: Rajan, Pavithra Cc: pgsql-general@xxxxxxxxxxxxxx Subject: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general