[thelist] MySQL search everywhere?

Luther, Ron Ron.Luther at hp.com
Thu Oct 15 08:00:24 CDT 2009

Rudy noted:

>>sorry stephen, it's not good news

>>SELECT * FROM table1
>>WHERE column1 LIKE '%foo%'
>>   OR column2 LIKE '%foo%
>>SELECT * FROM table2
>>WHERE column6 LIKE '%foo%'
>>   OR column7 LIKE '%foo%

Hi Rudy || Stephen,

Yes, but all is not quite that gloomy.   The SQL is, as Rudy pointed out, long and tedious to write by hand.  On the plus side, however, you can build all of that SQL pretty easily and quickly in a scripting language - so you don't have to type that all in by hand.

If my memory isn't too terribly rusty, there are some 'admin' or 'meta' tables in an Oracle db (or any other kind) that you can query to get a list of all of the tables in the db.  [ALL_TABLES? DBA_TABLES? USER_TABLES? Something like that I think.]  

There are also some data dictionary objects somewhere you can use to get a list of all of the fields in each individual table.  (Again, pretty standard stuff in any db technology.)

[I'm pretty sure this exact exercise (list all fields in all tables) is a pretty standard report in any db.]

But duh, why rely on my rusty memory when I have google handy!  ;-)

Google says this should get you a list of tables in an Oracle db:
select * from user_objects where object_type = 'TABLE';

Another quick google search says this should give you a list of the columns in a given Oracle table:
SELECT column_name
FROM user_tab_cols

String both of those items together in a loop or two within your favorite scripting language ... and viola ... you can autogenerate all the SQL Rudy outlined above.  Run that and you should be golden.


More information about the thelist mailing list