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
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
well, the note is about chr(0). Do you have chr(0) in your DB? And why?
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 > 3′. Right?
Mike
Hi Michael,
I apologize for the annoyance due to wordpress.
I will eventually replace wordpress search by google search.
Laurent
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
any reproducible example to post? version?
oracle release 10.2.0.1.0
string= 0b123a
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?
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
you should restrict the datatype to char then
… from user_tables where data_type=’CHAR’ …
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;
/
In addition to the query above, michaels posted this on otn :
http://forums.oracle.com/forums/message.jspa?messageID=3360446#3360446