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 Comments

  • 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

  • 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

  • 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

  • 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

  • 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

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


  • 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>

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

  • 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 ..

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

Leave a Reply