Search for a string across all tables in oracle

you'll have to user dynamic sql to query that:

1.- query all columns that contain text values
2.- build a PL/SQL code to search in all of them for the string requested.

Something like:

declare
    num_rows number;
    sql_text varchar2(250);
    sql_info varchar2(100);
begin
    dbms_output.enable(1000000);
    for x in (select table_name, column_name from dba_tab_columns
               where data_type in ('VARCHAR','VARCHAR2','CHAR')
                 and owner='SYSTEM')
    loop
         sql_text:='select count(*) into :num_rows from SYSTEM.'||x.table_name||' where '||x.column_name||' like ''%A%''';
         -- dbms_output.put_line (sql_text);
         execute immediate sql_text into num_rows;
         if num_rows>0 
         then 
              sql_info:='Table: '||x.table_name||' contains the string';
              dbms_output.put_line (sql_info);
         end if;
    end loop;
end;
/

Select allOpen in new window

Changing A for the string requested and SYSTEM for the schema being queried... Here's an example:

SQL> r
  1  declare
  2      num_rows number;
  3      sql_text varchar2(250);
  4      sql_info varchar2(100);
  5  begin
  6      dbms_output.enable(1000000);
  7      for x in (select table_name, column_name from dba_tab_columns
  8                 where data_type in ('VARCHAR','VARCHAR2','CHAR')
  9                   and owner='SYSTEM')
 10      loop
 11           sql_text:='select count(*) into :num_rows from SYSTEM.'||x.table_name||' where '||x.column_name||' like ''%A%''';
 12           -- dbms_output.put_line (sql_text);
 13           execute immediate sql_text into num_rows;
 14           if num_rows>0
 15           then
 16                sql_info:='Table: '||x.table_name||' contains the string';
 17                dbms_output.put_line (sql_info);
 18           end if;
 19      end loop;
 20* end;
Table: AQ$_INTERNET_AGENTS contains the string
Table: AQ$_INTERNET_AGENT_PRIVS contains the string
Table: AQ$_QUEUES contains the string
Table: AQ$_QUEUES contains the string
Table: AQ$_QUEUE_TABLES contains the string
Table: AQ$_QUEUE_TABLES contains the string
Table: AQ$_QUEUE_TABLES contains the string
Table: DEF$_DESTINATION contains the string
Table: HELP contains the string
Table: HELP contains the string
Table: LOGSTDBY$SKIP_SUPPORT contains the string
Table: MVIEW$_ADV_PARAMETERS contains the string
Table: REPCAT$_AUDIT_ATTRIBUTE contains the string
Table: REPCAT$_AUDIT_ATTRIBUTE contains the string
Table: REPCAT$_OBJECT_TYPES contains the string
Table: REPCAT$_RESOLUTION_METHOD contains the string
Table: REPCAT$_TEMPLATE_TYPES contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: SYS_EXPORT_FULL_01 contains the string
Table: TEMP_JAVA_PRIVS contains the string
Table: TEMP_JAVA_PRIVS contains the string
Table: TEMP_JAVA_PRIVS contains the string
Table: TEMP_JAVA_PRIVS contains the string
Table: TEMP_JAVA_PRIVS contains the string

Select allOpen in new window

'select count(*) into :num_rows from PBSDDEV.'||x.table_name||' where '||x.column_name||' like ''A'''

I don't want to check column names that start with A, but column data that start with say an error: ORA-01428

I want to check all columns in all tables that contain ORA-01428

To find columns containing an error message -
... ' where instr('||x.column_name||',''ORA-'') > 0)'
To find columns beginning with an error message -
... ' where instr('||x.column_name||',''ORA-'') = 1)'
To find columns containing a specific error message -
... ' where instr('||x.column_name||',''ORA-01428'') > 0)'

"Is there any metadata table that i can reference for this information?"
No.

Using a dynamic query in a PL\SQL procedure something like what techlevel suggested could work to answer this question.  But, be aware that the performance of a procedure like this could be *VERY* slow.  This will depend on the size of your database and on a number of other factors including:
1. the amount of RAM in the server
2. the size of the Oracle SGA or the value for buffer_cache
3. the number of CPUs
4. your disk system
etc.

A procedure like this could easily run for hours or days.  Also, you most likely don't want it limited to just tables in the "SYSTEM" schema, but maybe you do know the schema(s) you want to search.

See attached.
comments.txt

so the query is:

select owner, table_name , column_name ...
from all_tab_columns
where column_name like '%ERR%' or column_name like '%MSG%'

Only you know your table definitions, so you may also have a column like
 '%MESSAGE%' or the like that you would also include in the where clause.

Well, I see you accepted the solution that searches for columns with some text in the column name, not the string contained in the text values of the columns.

Your question suggested you requested something absolutely different.

Maybe I didn't make it clear but, what I was trying to get across is to limit the cursor query to select only such columns that might contain an error message and not all of the character type columns in the schema since that could be extremely poor performing. You would still need to loop through the columns in the cursor dynamically searching for the specified text value in those columns.

great!

See attached.
procedure.txt

See attached.
procedures.txt

This procedure only counts records from character columns
that will likely store error messages

declare
      v_cnt number := 0;
      v_tot number := 0;
      v_sql varchar2(255);
begin
      for rec in
            (select table_name, column_name
            from user_tab_columns
            where instr(data_type ,'CHAR') > 0
            and column_name like '%ERR%') <--I am not looking for those columns that are named : ERR
      loop
            v_sql := 'select count(*) from '||rec.table_name;
            execute immediate v_sql into v_cnt;
            v_tot := v_tot + v_cnt;
      end loop;
            dbms_output.put_line('Total count = '||to_char(v_tot));
end;

>>and column_name like '%ERR%') <--I am not looking for those columns that are named : ERR<<
 [sic] not named but like '%ERR%'
But I think you should be. Otherwise you will be needlessly looping through columns like firstname, lastname, address, city, state, and many others that will never contain an error message.

There is an open source tool that do exactly the job :
Search for JDBSearch on GitHub,  it's an open source java project so you can see the source code. or download a runnable.

Thanks it works for me  and I have created a modified version that gives you query to verify the results.

SET SERVEROUTPUT ON SIZE UNLIMITED;
   declare
       num_rows number;
       sql_text varchar2(2500);
       sql_info varchar2(1000);
   begin
       dbms_output.enable(1000000);
       for x in (select table_name, column_name,OWNER from ALL_tab_columns
                  where data_type in ('VARCHAR','VARCHAR2','CHAR')
                    and owner='SCOTT' order by owner,table_name,column_name)
        loop
             sql_text:='select count(0) into :num_rows from '||X.OWNER||'.'||x.table_name||' where '||x.column_name||' like ''%MANAGER%''';
             -- dbms_output.put_line (sql_text);
             execute immediate sql_text into num_rows;
             if num_rows>0
             then
                  sql_info:='SELECT X.'||X.COLUMN_NAME||',X.* FROM  '||X.OWNER||'.'||x.table_name||' X  ORDER BY X.'||X.COLUMN_NAME||'; -- '||X.COLUMN_NAME;
                  dbms_output.put_line (sql_info);
             end if;
        end loop;
    end;


SELECT X.ENAME,X.* FROM  SCOTT.EMP X  ORDER BY X.NAME; -- ENAME