Search all tables for a certain data

[j@vaguy]

All, I have an interesting problem for which I need some advice. I need to search an Oracle database for a piece of data...don't have the table name, or the column name...only have the data that needs to be searched (say for example "JOHN SMITH").

So how can I write a sproc, that will loop through each user table, then in each row/column and search for JOHN SMITH? If I am dealing with about 50 tables or so, would it just be better to use the Schema Browser in TOAD and just eye-ball it? Is there a tool available that would let me do some type of global search, for example like the Windows search? Thanks!!

[PAVB]

Nice business requirement.

What you need is a query that would write the query you need, here are the specs:

1- select from dba_tab_columns where schema is one of the schemas you want to search AND the column has a datatype like varchar2 -I suppose you don't want to search date and number columns, don't you?

2- rework your query to add the "select" syntax including a where clause in the form "liike '%JOHN SMITH%'.

3- last but not least don't forget to wrap column name on upper() function.

Your query should provide you one query for each searchable column.

[j@vaguy]

omg that worked so good!

Here is my query:

select 'select * from ' || table_name || ' where upper(' || column_name || ') LIKE ''%JOHN_SMITH%'''
  from dba_tab_columns
 where table_name LIKE 'NAME%' and
       data_type = 'VARCHAR2'

Like you said this gave one query for each searchable column, and we knew the tables we wanted were prefixed with NAME...there were like 500 results, which is fine because I just exported them to an Excel spreadsheet and split them up between the team to run . Thank you very much!

[PAVB]

glad it worked fine, you know... "to protect and serve"