6

Is it possible to search every field of every table for a particular value in Oracle.

I want to do it without using any procedure..

Can we do it with query?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Royson
  • 2,851
  • 13
  • 36
  • 50
  • 1
    possible duplicate of [Search All Fields In All Tables For A Specific Value (Oracle)](http://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle) – Tony Andrews Mar 04 '11 at 12:49

1 Answers1

12

You can do it with a single query though it's a bit convoluted. This query will search all CHAR and VARCHAR2 columns in the current schema for the string 'JONES'

select table_name,
       column_name
  from( select table_name,
               column_name,
               to_number(
                 extractvalue(
                   xmltype(
                     dbms_xmlgen.getxml(
                       'select count(*) c from ' || table_name ||
                       ' where to_char(' || column_name || ') = ''JONES'''
                     )
                   ),
                   'ROWSET/ROW/C'
                 )
               ) cnt
          from (select utc.*, rownum
                  from user_tab_columns utc
                 where data_type in ('CHAR', 'VARCHAR2') ) )
 where cnt >= 0

Note that this is an adapted version of the Laurent Schneider's query to count the rows in every table with a single query.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Works like a charm on 10g also. But if you chage the last 'where cnt>=0' to 'cnt > 0', it gives an error. To overcome that, just wrap the entire query from an outer query, like this : select x.* from (ABOVE QUERY) where x.cnt > 0 – Yohan Liyanage Mar 13 '12 at 09:19