Search All Fields In All Tables For A Specific Value (Oracle)

Question

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

There are hundreds of tables with thousands of rows in some tables so I know this could take a very long time to query. But the only thing I know is that a value for the field I would like to query against is '1/22/2008P09RR8'.

I've tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.

SELECT * from dba_objects WHERE object_name like '%DTN%'


There is absolutely no documentation on this database and I have no idea where this field is being pulled from.

Any thoughts???

Answer 1

I don't of a simple solution on the SQL promprt. Howeve there are quite a few tools like toad and PL/SQL Developer that have a GUI where a user can input the string to be searched and it will return the table/procedure/object where this is found.

Answer 2

Yes you can and your DBA will hate you and will find you to nail your shoes to the floor because that will cause lots of I/O and bring the database performance really down as the cache purges.

select column_name from all_tab_columns c, user_all_tables u where c.table_name = u.table_name;

for a start.

I would start with the running queries, using the v$session and the v$sqlarea. This changes based on oracle version. THis will narrow down the space and not hit everything.

Answer 3

I would do something like this (generates all the selects you need). You can later on feed them to sqlplus:

echo "select table_name from user_tables;" | sqlplus -S user/pwd |grep -v "^--" | grep -v "TABLE_NAME" | grep "^[A-Z]" | while read sw; do echo "desc $sw" | sqlplus -S user/pwd | grep -v "------" | awk -F' ' '{print $1}' | while read nw; do echo "select * from $sw where $nw='val'"; done; done

It yields:

select * from TBL1 where DESCRIPTION='val'

select * from TBL1 where ='val'

select * from TBL2 where Name='val'

select * from TBL2 where LNG_ID='val'

And what it does is - for each table_name from user_tables get each field (from desc) and create a select * from table where field equals 'val'

Answer 4

Quote:

I've tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.*

SELECT * from dba_objects WHERE
object_name like '%DTN%'

A column isn't an object. If you mean that you expect the column name to be like '%DTN%', the query you want is:

SELECT table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%';

But if the 'DTN' string is just a guess on your part, that probably won't help.

By the way, how certain are you that '1/22/2008P09RR8' is a value selected directly from a single column? If you don't know at all where it is coming from, it could be a concatenation of several columns, or the result of some function, or a value sitting in a nested table object. So you might be on a wild goose chase trying to check every column for that value. Can you not start with whatever client application is displaying this value and try to figure out what query it is using to obtain it?

Anyway, diciu's answer gives one method of generating SQL queries to check every column of every table for the value. You can also do similar stuff entirely in one SQL session using a PL/SQL block and dynamic SQL. Here's some hastily-written code for that:

    SET SERVEROUTPUT ON SIZE 100000

    DECLARE
      match_count INTEGER;
    BEGIN
      FOR t IN (SELECT table_name, column_name FROM all_tables) LOOP

        EXECUTE IMMEDIATE
          'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'
          INTO match_count
          USING '1/22/2008P09RR8';

        IF match_count > 0 THEN
          dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
        END IF;

      END LOOP;

    END;
    /

There are some ways you could make it more efficient too.

In this case, given the value you are looking for, you can clearly eliminate any column that is of NUMBER or DATE type, which would reduce the number of queries. Maybe even restrict it to columns where type is like '%CHAR%'.

Instead of one query per column, you could build one query per table like this:

SELECT * FROM table1
  WHERE column1 = 'value'
     OR column2 = 'value'
     OR column3 = 'value'
     ...
     ;

Answer 5

Thank you, Chris Conway!

The query worked for Oracle 10 with modification. Even a generalized procedure is found at http://it.toolbox.com/blogs/david/search-for-a-given-string-in-all-fields-of-an-entire-schema-24074

Answer 6

I modified this procedure a little to reduce the number of tables I want to search for and executed it in toad I get the following error

Error at line 2

ORA-01858: a non-numeric character was found where a numeric was expected

ORA-06512: at line 5

SET SERVEROUTPUT ON size 100000;
declare
  match_count integer;
BEGIN
  FOR t IN (SELECT table_name, column_name FROM all_tab_columns where table_name in (select UPPER(assettype)from assettype)) LOOP
    EXECUTE IMMEDIATE
      'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'
      INTO match_count
      USING 'xmro.xmradio.com';
    IF match_count > 0 THEN
      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
    END IF;
  END LOOP;
END;
/

Answer 7

There are some free tools that make these kind of search, for example, this one works fine and source code is available. http://sites.google.com/site/freejansoft/dbsearch-1

you'll need the Oracle ODBC driver and a DSN to use this tool