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

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 WHEREobject_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 owner, 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 owner, table_name, column_name                  FROM all_tab_columns                  WHERE owner <> 'SYS' and data_type LIKE '%CHAR%') LOOP        EXECUTE IMMEDIATE          'SELECT COUNT(*) FROM ' || t.owner || '.' || 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'     ...     ;

I did some modification to the above code to make it work faster if you are searching in only one owner.You just have to change the 3 variables v_owner, v_data_type and v_search_string to fit what you are searching for.

SET SERVEROUTPUT ON SIZE 100000DECLARE  match_count INTEGER;-- Type the owner of the tables you are looking at  v_owner VARCHAR2(255) :='ENTER_USERNAME_HERE';-- Type the data type you are look at (in CAPITAL)-- VARCHAR2, NUMBER, etc.  v_data_type VARCHAR2(255) :='VARCHAR2';-- Type the string you are looking at  v_search_string VARCHAR2(4000) :='string to search here...';BEGIN  FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP    EXECUTE IMMEDIATE     'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'    INTO match_count    USING v_search_string;    IF match_count > 0 THEN      dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );    END IF;  END LOOP;END;/

I know this is an old topic. But I see a comment to the question asking if it could be done in SQL rather than using PL/SQL. So thought to post a solution.

The below demonstration is to Search for a VALUE in all COLUMNS of all TABLES in an entire SCHEMA:

• Search a CHARACTER type

Let's look for the value KING in SCOTT schema.

SQL> variable val varchar2(10)SQL> exec :val := 'KING'PL/SQL procedure successfully completed.SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",  2    SUBSTR (table_name, 1, 14) "Table",  3    SUBSTR (column_name, 1, 14) "Column"  4  FROM cols,  5    TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '  6    || column_name  7    || ' from '  8    || table_name  9    || ' where upper(' 10    || column_name 11    || ') like upper(''%' 12    || :val 13    || '%'')' ).extract ('ROWSET/ROW/*') ) ) t 14  ORDER BY "Table" 15  /Searchword  Table          Column----------- -------------- --------------KING        EMP            ENAMESQL>

• Search a NUMERIC type

Let's look for the value 20 in SCOTT schema.

SQL> variable val NUMBERSQL> exec :val := 20PL/SQL procedure successfully completed.SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",  2    SUBSTR (table_name, 1, 14) "Table",  3    SUBSTR (column_name, 1, 14) "Column"  4  FROM cols,  5    TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '  6    || column_name  7    || ' from '  8    || table_name  9    || ' where upper(' 10    || column_name 11    || ') like upper(''%' 12    || :val 13    || '%'')' ).extract ('ROWSET/ROW/*') ) ) t 14  ORDER BY "Table" 15  /Searchword  Table          Column----------- -------------- --------------20          DEPT           DEPTNO20          EMP            DEPTNO20          EMP            HIREDATE20          SALGRADE       HISAL20          SALGRADE       LOSALSQL>