Oracle Search all tables all columns for string

Question

I need to search our oracle database for a string in all tables and columns. I have the below query I found online but when I execute it I get the following error

Any help is appreciated

ORA-06550: line 6, column 31:
PL/SQL: ORA-00904: "COLUMN_NAME": invalid identifier
ORA-06550: line 6, column 12:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 30:
PLS-00364: loop index variable 'T' use is invalid
ORA-06550: line 7, column 4:
PL/SQL: Statement ignored
ORA-06550: line 12, column 38:
PLS-00364: loop index variable 'T' use is invalid
ORA-06550: line 12, column 16:
PL/SQL: Statement ignored

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

Why don't you search the database files instead using some manner of grep? At least then you could get file/block offsets, and go find the tables in question. — Adam Musch, Jun 17 '11 at 18:20

Justin Cave · Accepted Answer · 2012-01-19T18:50:49.900

At a minimum, you need to query ALL_TAB_COLUMNS, not ALL_TABLES

DECLARE
  match_count integer;
  v_search_string varchar2(4000) := <<string you want to search for>>;
BEGIN  
  FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns) LOOP   
    EXECUTE IMMEDIATE    
      'SELECT COUNT(*) FROM '||t.owner || '.' || 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.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
    END IF; 
  END LOOP;
END;
/

If you are looking for a string, however, you would almost certainly want to restrict yourself to looking for columns that could store a string. It wouldn't make sense, for example, to search a DATE column for a string. And unless you have a great deal of a priori knowledge about what a BLOB column contains and the ability to parse the BLOB column's binary formatting, it wouldn't make sense to search a BLOB column for a string. Given that, I suspect you want something more like

DECLARE
  match_count integer;
  v_search_string varchar2(4000) := <<string you want to search for>>;
BEGIN  
  FOR t IN (SELECT owner,
                   table_name, 
                   column_name 
              FROM all_tab_columns
             WHERE data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2', 
                                 'CLOB', 'NCLOB') ) 
  LOOP   
    BEGIN
      EXECUTE IMMEDIATE    
        'SELECT COUNT(*) FROM '||t.owner || '.' || 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.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
      END IF; 
    EXCEPTION
      WHEN others THEN
        dbms_output.put_line( 'Error encountered trying to read ' ||
                              t.column_name || ' from ' || 
                              t.owner || '.' || t.table_name );
    END;
  END LOOP;
END;
/

Of course, this is going to be insanely slow-- you'd full scan every table once for every string column in the table. With moderately large tables and a moderate number of string columns, that is likely to take quite a while.

That works, a bit through the query it gives me the below error of ORA-00942: table or view does not exist ORA-06512: at line 12 How can I have it resume or ignore this error — Jim, Jun 17 '11 at 18:01
@Jim - I added an exception handler to my second solution. I also added the schema owner qualifier in case you have access to tables outside of your schema. — Justin Cave, Jun 17 '11 at 18:37
I had to declare v_search_string and match_count, and add the owner field in the SELECT in the implicit cursor to get this work. This is real gem +1. — Blanthor, Jan 19 '12 at 18:44
@Blanthor - Good point. I added the local variable declarations and fixed the query. Thanks! — Justin Cave, Jan 19 '12 at 18:52
Probably this is stupid question, but does this search query do case sensitive search or not? — Mikhail, Mar 24 '14 at 15:29
@Mikhail - In general, this would do a case sensitive search (queries in Oracle are case-sensitive in general). If you've set your `NLS_SORT` and `NLS_COMP` for your session to do case-insensitive searches, however, this would do case-insensitive searches. — Justin Cave, Mar 24 '14 at 15:44
@JustinCave How can I change these? I tried to use alter session set NLS_COMP=ANSI + alter session NLS_SORT=BINARY_CI but looks like it doesn't work for me. Also I noticed that your query doesn't return value I'm searching, which certainly exist in the database (my search string looks like '%value1%')... Does this query is universal one or I need to take care about some adjustments for my database? — Mikhail, Mar 26 '14 at 07:54
@Mikhail - Case insensitive searching does not work with the `LIKE` operator, you'd need to specify the case correctly. You could, of course, modify the query to be `WHERE lower(' || t.column_name || ') like lower(%1)`. Or you could use the `regexp_like` function which accepts a case sensitivity argument as its third parameter. — Justin Cave, Mar 26 '14 at 19:35

Oracle Search all tables all columns for string

1 Answers1

Linked

Related