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?

Solution 1

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 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'
     ...
     ;

Solution 2

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 100000

DECLARE
  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;
/

Solution 3

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            ENAME

SQL>

• Search a NUMERIC type

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

SQL> variable val NUMBER
SQL> exec :val := 20

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
----------- -------------- --------------
20          DEPT           DEPTNO
20          EMP            DEPTNO
20          EMP            HIREDATE
20          SALGRADE       HISAL
20          SALGRADE       LOSAL

SQL>

Solution 4

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.

Solution 5

Here is another modified version that will compare a lower substring match. This works in Oracle 11g.

DECLARE
  match_count INTEGER;
-- Type the owner of the tables you are looking at
  v_owner VARCHAR2(255) :='OWNER_NAME';

-- 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) :='%lower-search-sub-string%';

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 lower('||t.column_name||') like :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;
/

View more solutions

Comments

[Erich Kitzmueller]

You should restrict it to char, varchar and varchar2 columns, since number and date columns cannot possibly contain that string.

[Dave Costa]

@ammoQ -- like I said in the second-to-last paragraph?

[Regmi]

I ran this on 9i and I get column_name unknown error. Can someone tell me what modification will be required to run this on 9i?

[Dave Costa]

@Regmi -- sorry, that was actually a mistake in my code, not a version issue. The loop should have been driven by all_tab_columns not all_tables. I've fixed it.

[Regmi]

@DaveCosta - Thanks for the fix but I still get 'table or view does not exist' error on line 6. Line 6 being "Execute Immediate".

[Dave Costa]

@Regmi - Made a couple more enhancements. Take owner into account, which is probably the cause of your current error. I also filtered out everything owned by SYS for performance, and restricted to character data types to avoid numeric comparison errors.

[Freakyuser]

Can we do this using a single query instead of using a stored procedure?

[Dave Costa]

@Freakyuser - technically the above is not a stored procedure, it's an anonymous block. But I take it you would prefer a single SQL query. I can't think of a way to do that for any non-trivial set of tables; since the table and column names will vary, you need to use dynamic SQL. However, if you used my code as the basis for a pipelined function, you could then select from that function as a row source, so you would be able to get the results as the output of a query.

[Lalit Kumar B]

The same could be done in SQL using xmlsequence. And dbms_output should be avoided in production systems.

[towi]

hrmm.... using xml looks like overkill. Besides: Error occurred in XML processing ORA-00932: inconsistent datatypes: expected NUMBER got BLOB

[Popa Andrei]

I had to comment the first line in order to be able to run this query. Also I was not able to remove the owner filter and run the query.

[Soumitri Pattnaik]

@DaveCosta how can I perform a like search in the same sql?

[Dave Costa]

@SoumitriPattnaik you'd probably change = to LIKE in the dynamic SQL statement.

[Mohammad Faisal]

ORA-19202: Error occurred in XML processing ORA-00932: inconsistent datatypes: expected CHAR got BLOB ORA-06512: at "SYS.DBMS_XMLGEN", line 288 ORA-06512: at line 1 19202. 00000 - "Error occurred in XML processing%s" *Cause: An error occurred when processing the XML function *Action: Check the given error message and fix the appropriate problem

[Menelaos]

Any Ideas? ORA-19202: Error occurred in XML processing ORA-22813: operand value exceeds system limits ORA-06512: at "SYS.DBMS_XMLGEN", line 288 ORA-06512: at line 1

[Steve Chambers]

I needed to put double quotes around the table name / column name to avoid issues when these need to be quoted: 'SELECT COUNT(*) FROM "'||t.table_name||'" WHERE "'||t.column_name||'" = :1'

[phil_w]

Watch out that all_tab_cols also contains views, despite the name

[misteeque]

what exactly is dbms_output? Because the queries get executed successfully in DataGrip, but I do not see any result after.

[user1286399]

I am getting the error 00904 - "Name" : invalid identifier with both solutions Lalit Kumar and @DaveCosta due to the database design. Where should I add the clause to overcome it?

[Dave Costa]

@user1286399 Sounds like you might have a mixed-case column name. If so, you need to modify the dynamic SQL statements to put double quotes around the column name.

[JasonWH]

I know this is a bit old, but when I run this I just get a Script Output of "anonymous block completed"

[Mikhail T.]

Breaks on LONG BINARY columns :(

[Tahir]

Error report - ORA-00933: SQL command not properly ended ORA-06512: at "TBOWNER.SEARCH_DB", line 17 ORA-06512: at "TBOWNER.SEARCH_DB", line 17 ORA-06512: at line 6 00933. 00000 - "SQL command not properly ended"

[Tahir]

I am getting above error, while running the procedure. Running like this DECLARE SEARCH_STR VARCHAR2(200); TAB_COL_RECS VARCHAR2(200); BEGIN SEARCH_STR := 'REQ000000839496'; SEARCH_DB( SEARCH_STR => SEARCH_STR, TAB_COL_RECS => TAB_COL_RECS ); DBMS_OUTPUT.PUT_LINE('TAB_COL_RECS = ' || TAB_COL_RECS); END;

[Ak777]

As of this year 2022, this should be the answer marked. :-) thanks and this works like charm! on Oracle 10g, 19c.