« To exp or to expdp?
length('’)=null? »

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

This entry was posted on Wednesday, December 20th, 2006 at 10:33 and is filed under oracle, sql, dba, xml. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

16 Responses to “search for a string in all tables of a schema”

  1. Pesi Says:
    December 20th, 2006 at 13:45

    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

  2. laurentschneider Says:
    December 20th, 2006 at 15:56

    well, the note is about chr(0). Do you have chr(0) in your DB? And why?

  3. Michael Moore Says:
    April 20th, 2007 at 22:06

    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 Says:
    April 20th, 2007 at 22:13

    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

  5. laurentschneider Says:
    April 21st, 2007 at 07:49

    Hi Michael,
    I apologize for the annoyance due to wordpress.
    I will eventually replace wordpress search by google search.
    Laurent

  6. laurentschneider Says:
    April 27th, 2007 at 10:28

    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 comVPТ"g&F%6V0Т2vW&RF%6V2F&US6V&6F&RF&UPТBBF%6^umns.owner=search_in_table.owner
 15            and data_type like '%CHAR%' )
 16    loop
 17      stmt := stmt || 'max(decode("' || c.comVRr"rrrТ6V&67G&rrrrrrr26VRrrrsТVBТ#7FC7FGvf"6VBVV’g&"wТ#vW'r""wF&UWr"sТ#"WV7WFRVFFR7FBF6ðТ#2&WGW&6ðТ#BVCТ#RРФgV7F7&VFVBРХ5Х56rW' ФW'&'2Х5Х5Х56VV7BF&UR62g&Т"6VV7B&vVТ2F&URТB6V&6F&RtTD$rF&URtrr60ТRg&F&W2vW&RvW#tTD$rТbvW&R622BVðХD$UR40ТФTTPХB0ХC"33 Р

  7. Itziks Says:
    June 24th, 2007 at 14:31

    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

  8. Laurent Schneider Says:
    June 24th, 2007 at 21:38

    any reproducible example to post? version?

  9. Itziks Says:
    June 25th, 2007 at 13:01

    oracle release 10.2.0.1.0
    string= 0b123a

  10. Laurent Schneider Says:
    June 25th, 2007 at 13:19

    in which table does the string exists? what is the datatype? is the string the value of the column, or is it only part of it?

  11. Itziks Says:
    June 26th, 2007 at 12:35

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

  12. Laurent Schneider Says:
    June 26th, 2007 at 12:49


    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 Says:
    June 27th, 2007 at 06:20

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

  14. Laurent Schneider Says:
    June 27th, 2007 at 10:32

    you should restrict the datatype to char then

    … from user_tables where data_type=’CHAR’ …

  15. Karthik Says:
    July 20th, 2007 at 05:58

    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 Says:
    August 3rd, 2007 at 17:18

    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

Use <code> and </code> to post code