search for a string in all tables of a schema

this is often asked on the forums. I also needed this a while ago while reverse engineering a database model.

Here is my today solution:

1) select * and extract the first column found per table with regexp (10g)

SQL> select table_name,column_name from (select rownum,table_name, regexp_substr(dbms_xmlgen.getxml(‘select * from “‘||table_name||'”‘),'<[^>]*>&string</[^<]*>’) column_name from user_tables) where length(column_name)!=0;
Enter value for string: 20
TABLE_NAME COLUMN_NAME
———- ——————————
DEPT       <DEPTNO>20</DEPTNO>
EMP        <DEPTNO>20</DEPTNO>

SQL> select table_name,column_name from (select rownum,table_name, regexp_substr(dbms_xmlgen.getxml(‘select * from “‘||table_name||'”‘),'<[^>]*>&string</[^<]*>’) column_name from user_tables) where length(column_name)!=0;
Enter value for string: KING
TABLE_NAME COLUMN_NAME
———- ——————————
EMP        <ENAME>KING</ENAME>
BONUS      <ENAME>KING</ENAME>

2) add a where condition. much slower of course, because scanning the table more often. somehow nicer output. More sensible to datatypes. Here for number.

SQL> select table_name, column_name from (select rownum,table_name, column_name, dbms_xmlgen.getxml(‘select 1 from “‘||table_name||'” where “‘||column_name||'”=&number’) x from user_tab_columns where data_type=’NUMBER’) where length(x)!=0;
Enter value for number: 3000
TABLE_NAME COLUMN_NAME
———- ——————————
EMP        SAL
BONUS      COMM
SALGRADE   HISAL

18 thoughts on “search for a string in all tables of a schema”

  1. Pesi

    Hi Laurent,

    Thanks for this entry, something that is VERY USEFUL but unfortunately there seems to be a bug when NLS_LANG is set to UTF8. See Note:304921.1 on Metalink. You don’t happen to have solutions when having NLS_LANG set to UTF8?

    Best regards,
    Pesi

  3. Michael Moore

    Very nice! One tiny thing; When you enter your string for searching, you would need to allow for characters like > 3′, you would need to instead search for ‘5 > 3’. Right?

    Regards,
    Mike

  4. Michael Moore

    Ok, my last message did not come out the way I typed it. Apparently, wordpress is not properly escaping special characters. I will try again.

    When you enter your string for searching, you would need to allow for characters like < > and &. Instead of “5 > 3”, you would need to instead search for “5 &gt; 3′. Right?

    Mike

  6. laurentschneider Post author

    If you want a NO-XML solution, you could try :

    SQL> create or replace function search_in_table(
    2 owner varchar2,
    3 table_name varchar2,
    4 search_string varchar2)
    5 return varchar2 is
    6 stmt varchar2(4000);
    7 col varchar2(4000);
    8 begin
    9 stmt := ‘select ‘;
    10 for c in (
    11 select column_name
    12 from all_tab_columns
    13 where all_tab_columns.table_name=search_in_table.table_name
    14 and all_tab_columns.owner=search_in_table.owner
    15 and data_type like ‘%CHAR%’ )
    16 loop
    17 stmt := stmt || ‘max(decode(“‘ || c.column_name || ‘”,”’ ||
    18 search_string || ”’,”’ || c.column_name || ‘ ”))||’;
    19 end loop;
    20 stmt:= stmt||’nvl2(count(*),null,null) from “‘||
    21 owner||'”.”‘||table_name||'”‘;
    22 execute immediate stmt into col;
    23 return col;
    24 end;
    25 /

    Function created.

    SQL>
    SQL> show err
    No errors.
    SQL>
    SQL>
    SQL> select table_name,cols from (
    2 select rownum,
    3 table_name,
    4 search_in_table(‘AUDBA’,table_name,’KING’) cols
    5 from all_tables where owner=’AUDBA’)
    6 where cols is not null;
    TABLE_NAME COLS
    ———- ———————————————————-
    EMP ENAME
    T C
    T2 C1 C2

  7. Itziks

    I was tring to run the search string query but I didn’t get any result even on string that I know that exist, I’m using sqlplus

  11. Itziks

    The type is char(16)
    the string = 0b18000432c21111
    I’m looking for the whole string

  12. Laurent Schneider Post author


    drop table t;
    create table t(x char(16));
    insert into t values(‘0b18000432c21111’);
    select table_name,column_name from (
    select rownum,table_name, regexp_substr(dbms_xmlgen.getxml(
    ‘select * from “‘||table_name||'”‘),'<[^>]*>0b18000432c21111</[^<]*>’) column_name
    from user_tables)
    where length(column_name)!=0;
    TABLE_NAME COLUMN_NAME
    —————————— ———————————————————————————-
    T <X>0b18000432c21111</X>

  13. Itziks

    now I getting an Error:
    ORA-24812: character set conversion to or from UCS2 failed

  15. Karthik

    search for a string in all tables of a schema is working fine for me. But it takes much time. Please suggest me to get the faster result ..

  16. David

    Hi Laurent,

    Thank you so much for the SQL and PL/SQL here.
    This was almost what I wanted.
    I needed to do it in a 9i database.
    So, I tried using your NO-XML solution because
    9i doesn’t have the “regexp_substr” function.
    Also, I wanted to use the wildcard ‘%’ in my
    search (again, since 9i doesn’t have the
    regular expression function).

    So, I modified your code slightly to use
    the “CASE” statement in your NO-XML solution
    and it works great.
    I included my change below.
    Thanks again,
    David
    This was such a time saver.

    —
    create or replace function search_in_table(
    owner varchar2,
    table_name varchar2,
    search_string varchar2)
    return varchar2 is
    stmt varchar2(32700);
    col varchar2(32700);
    begin
    stmt := ‘select ‘;
    for c in (
    select column_name
    from all_tab_columns
    where all_tab_columns.table_name=search_in_table.table_name
    and all_tab_columns.owner=search_in_table.owner
    and data_type like ‘%CHAR%’ )
    loop
    stmt := stmt || ‘max(case when “‘ || c.column_name ||
    ‘” LIKE ”’ || search_string || ”’ then ”’ || c.column_name
    || ‘ ”’ || ‘ else null end) || ‘;
    end loop;
    stmt:= stmt||’nvl2(count(*),null,null) from “‘||
    owner||'”.”‘||table_name||'”‘;
    execute immediate stmt into col;
    return col;
    end;
    /

Comments are closed.