Thanks for the question.

Asked: August 29, 2002 - 8:16 am UTC

Last updated: February 02, 2011 - 7:36 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi tom

I want to find the table_name and columns which contain a specific text.

For example if the user inputs a word India
The table_name and the column name which contain the word should be displayed.

*****Update*****

I want to search every column of every table in a schema looking for ones that have a row with the word "%INDIA%" in it

*****************

thanks and regards
Lakshmi

and Tom said...

You can start with this snippet:

</code> https://asktom.oracle.com/magazine-archive.htm <code>

You'll want to modify it to skip over numeric columns -- lobs/raws and such (easy to add a where datatype in ( 'VARCHAR2', 'CHAR' ) to the process!

Be aware -- it isn't going to be *fast*. Another enhancement to it would be to make it search for all columns in a table at the same time (instead of a full scan per column).

Oh well, might as well just do it then:

ops$tkyte@ORA920.US.ORACLE.COM> create or replace
2 procedure find_string( p_str in varchar2 )
3 authid current_user
4 as
5 l_query long;
6 l_case long;
7 l_runquery boolean;
8 l_tname varchar2(30);
9 l_cname varchar2(30);
10 begin
11 dbms_application_info.set_client_info( '%' || upper(p_str) || '%' );
12
13 for x in (select * from user_tables )
14 loop
15 l_query := 'select ''' || x.table_name || ''', $$
16 from ' || x.table_name || '
17 where rownum = 1 and ( 1=0 ';
18 l_case := 'case ';
19 l_runquery := FALSE;
20 for y in ( select *
21 from user_tab_columns
22 where table_name = x.table_name
23 and data_type in ( 'VARCHAR2', 'CHAR' )
24 )
25 loop
26 l_runquery := TRUE;
27 l_query := l_query || ' or upper(' || y.column_name ||
28 ') like userenv(''client_info'') ';
29 l_case := l_case || ' when upper(' || y.column_name ||
30 ') like userenv(''client_info'') then ''' ||
31 y.column_name || '''';
32 end loop;
33 if ( l_runquery )
34 then
35 l_case := l_case || ' else NULL end';
36 l_query := replace( l_query, '$$', l_case ) || ')';
37 begin
38 execute immediate l_query into l_tname, l_cname;
39 dbms_output.put_line
40 ( 'Found in ' || l_tname || '.' || l_cname );
41 exception
42 when no_data_found then
43 dbms_output.put_line
44 ( 'No hits in ' || x.table_name );
45 end;
46 end if;
47
48 end loop;
49 end;
50 /

Procedure created.

ops$tkyte@ORA920.US.ORACLE.COM> grant execute on find_string to public;
Grant succeeded.

ops$tkyte@ORA920.US.ORACLE.COM> @connect scott/tiger

scott@ORA920.US.ORACLE.COM> exec ops$tkyte.find_string( 'kin' )
No hits in BONUS
No hits in DEPT
Found in EMP.ENAME

PL/SQL procedure successfully completed.

Rating

(13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Problem with searching a value in multiple columns in a table

sai, August 30, 2002 - 12:46 pm UTC

Tom,

I used your procedure "find_string" to find out columns in a table that has same values. It doesn't seem to work for more than one column in a table having the same value.
For eg:-

SQL>create table test(eno number,ename varchar2(20),prof varchar2(20));

Table created.

SQL>insert into test values(1,'aa','aa');

1 row created.

SQL>insert into test values(2,'bbb','ccc');

1 row created.

SQL>commit;

Commit complete.

SQL>exec find_string('aa');
Found in TEST.ENAME

PL/SQL procedure successfully completed.

I have the value 'aa' in both ENAME  and PROF columns,
But it finds it only in ENAME.

Is there anything wrong in the way I did it , 
please explain ?

Thanks,
Sai

August 30, 2002 - 4:17 pm UTC

That is exactly the way I designed it -- it stops immediately upon finding the first occurrence in the first column.


I took the approach that simply "knowing" would be enough.  


So, here is an even slower.... one.  It full scans every single table and reports multiple hits (it uses a distinct so the number of messages/table is a function of the number of COLUMNS in the table -- not the number of rows that contain the string)

ops$tkyte@ORA920.US.ORACLE.COM> create or replace
  2  procedure find_string( p_str in varchar2 )
  3  authid current_user
  4  as
  5      l_query    long;
  6      l_case     long;
  7      l_runquery boolean;
  8      l_tname    varchar2(30);
  9      l_cname    varchar2(30);
 10          type rc is ref cursor;
 11          l_cursor   rc;
 12  begin
 13      dbms_application_info.set_client_info( '%' || upper(p_str) || '%' );
 14
 15      for x in (select * from user_tables )
 16      loop
 17          l_query := 'select distinct ''' || x.table_name || ''', $$
 18                        from ' || x.table_name || '
 19                       where ( 1=0 ';
 20          l_runquery := FALSE;
 21                  l_case     := NULL;
 22          for y in ( select *
 23                       from user_tab_columns
 24                      where table_name = x.table_name
 25                        and data_type in ( 'VARCHAR2', 'CHAR' )
 26                   )
 27          loop
 28              l_runquery := TRUE;
 29              l_query := l_query || ' or upper(' || y.column_name ||
 30                         ') like userenv(''client_info'') ';
 31              l_case := l_case || '||'' ''|| case when upper(' || y.column_name ||
 32                        ') like userenv(''client_info'') then ''' ||
 33                        y.column_name || ''' else NULL end';
 34          end loop;
 35          if ( l_runquery )
 36          then
 37              l_query := replace( l_query, '$$', substr(l_case,8) ) || ')';
 38              begin
 39                                  open l_cursor for l_query;
 40                                  loop
 41                                          fetch l_cursor into l_tname, l_cname;
 42                                          exit when l_cursor%notfound;
 43                          dbms_output.put_line
 44                          ( 'Found in ' || l_tname || '.' || l_cname );
 45                                  end loop;
 46                                  close l_cursor;
 47              end;
 48          end if;
 49
 50      end loop;
 51  end;
 52  /

Procedure created.

ops$tkyte@ORA920.US.ORACLE.COM> show err
No errors.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> grant execute on find_string to public;

Grant succeeded.

ops$tkyte@ORA920.US.ORACLE.COM> @connect scott/tiger
ops$tkyte@ORA920.US.ORACLE.COM> set termout off
scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM> set termout on
scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM> drop table t;

Table dropped.

scott@ORA920.US.ORACLE.COM> create table t ( x varchar2(25), y varchar2(25) );

Table created.

scott@ORA920.US.ORACLE.COM> insert into t values ( 'KING', 'xxx' );

1 row created.

scott@ORA920.US.ORACLE.COM> insert into t values ( 'xxx', 'KING' );

1 row created.

scott@ORA920.US.ORACLE.COM> insert into t values ( 'KING', 'KING' );

1 row created.

scott@ORA920.US.ORACLE.COM> exec ops$tkyte.find_string( 'kin' )
Found in EMP.ENAME
Found in T. Y
Found in T.X
Found in T.X Y

PL/SQL procedure successfully completed.

SQL TO RETRIVE A COLUMNS

Richard, January 29, 2003 - 1:50 pm UTC

Tom,

This would be very useful but when I tried your code but I got the following:

SQL> create procedure find_string( p_str in varchar2 ) 
  2  authid current_user 
  3  as 
  4      l_query    long; 
  5      l_case     long; 
  6      l_runquery boolean; 
  7      l_tname    varchar2(30); 
  8      l_cname    varchar2(30); 
  9          type rc is ref cursor; 
 10          l_cursor   rc; 
 11  begin 
 12      dbms_application_info.set_client_info( '%' || upper(p_str) || '%' ); 
 13   
 14      for x in (select * from user_tables ) 
 15      loop 
 16          l_query := 'select distinct ''' || x.table_name || ''', $$ 
 17                        from ' || x.table_name || ' 
 18                       where ( 1=0 '; 
 19          l_runquery := FALSE; 
 20                  l_case := NULL; 
 21          for y in ( select * 
 22                       from user_tab_columns 
 23                      where table_name = x.table_name 
 24                        and data_type in ( 'VARCHAR2', 'CHAR' ) 
 25                   )
 26          loop 
 27             l_runquery := TRUE; 
 28             l_query := l_query || ' or upper(' || y.column_name || 
 29                        ') like userenv(''client_info'') '; 
30             l_case := l_case || '||'' ''|| case when upper(' || y.column_name || ') like userenv(''client_info'') then ''' || y.column_name || ''' else NULL end'; 
31          end loop; 
32          if ( l_runquery ) 
33          then 
34             l_query := replace( l_query, '$$', substr(l_case,8) ) || ')'; 
35             begin 
36                 open l_cursor for l_query; 
37                     loop 
38                         fetch l_cursor into l_tname, l_cname; 
39                         exit when l_cursor%notfound; 
40                         dbms_output.put_line 
41                         ( 'Found in ' || l_tname || '.' || l_cname ); 
42                     end loop; 
43                 close l_cursor; 
44             end; 
45          end if; 
46     end loop; 
47  end; 
48  /

Procedure created.

SQL> EXECUTE FIND_STRING ('SANDWICH');
BEGIN FIND_STRING ('SANDWICH'); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYSADM.FIND_STRING", line 38
ORA-06512: at line 1


SQL> EXECUTE FIND_STRING ( 'SANDWICH' );
BEGIN FIND_STRING ( 'SANDWICH' ); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYSADM.FIND_STRING", line 38
ORA-06512: at line 1

January 29, 2003 - 2:01 pm UTC

so, when you added some debug and checked out what table/column it failed on -- what did you discover?  

What I mean is -- what have you done so far to try and figure it out?  Be great if you did and contributed a fix??


but I think I see the problem -- if you have lots of columns with sandwich in it in a single row -- we are just glueing them together...  so when I made the quick fix to search for all -- not just the first, it could overflow the cname size....

(but you know, 30 seconds of debugging on your part....)

So, for example:

ops$tkyte@ORA817DEV> create table t
  2  ( some_column        varchar2(25) default 'foo',
  3    some_column1       varchar2(25) default 'foo',
  4    some_column2       varchar2(25) default 'foo',
  5    some_column3       varchar2(25) default 'foo',
  6    some_column4       varchar2(25) default 'foo',
  7    some_column5       varchar2(25) default 'foo',
  8    some_column6       varchar2(25) default 'foo',
  9    some_column7       varchar2(25) default 'foo',
 10    some_column8       varchar2(25) default 'foo'
 11  );

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into t (some_column) values ('bar' );

1 row created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> exec find_string( 'bar' );
Found in T.SOME_COLUMN

PL/SQL procedure successfully completed.
<b>that works but:</b>


ops$tkyte@ORA817DEV> exec find_string( 'foo' );
BEGIN find_string( 'foo' ); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "OPS$TKYTE.FIND_STRING", line 40
ORA-06512: at line 1
<b>doesn't cause we are glueing lots of cnames together so, change:</b>

  6      l_runquery boolean;
  7      l_tname    varchar2(30);<b>
  8      l_cname    varchar2(4000);</b>
  9          type rc is ref cursor;
 10          l_cursor   rc;


<b>and wah-lah:</b>

ops$tkyte@ORA817DEV> exec find_string( 'foo' );
Found in T. SOME_COLUMN1 SOME_COLUMN2 SOME_COLUMN3 SOME_COLUMN4 SOME_COLUMN5 SOME_COLUMN6 SOME_COLUMN7 SOME_COLUMN8

PL/SQL procedure successfully completed.




(now aren't you all glad I didn't use a when others to do "error handling" ;)  we'd never have known of this problem....)

SQL TO RETRIVE A COLUMNS

Richard, January 29, 2003 - 1:57 pm UTC

Tom,

When I tried the snippet, referred to by the hyperlink, I go the following upon execution:

execute find_string ;

Enter value for giv_str: SANDWICH
old 3: giv_str varchar(200):='&giv_str';
new 3: giv_str varchar(200):='SANDWICH';
Table Name Column
Name
----------------------------- -----------------------------
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes
ORA-06512: at line 13

January 29, 2003 - 2:04 pm UTC

....
You'll want to modify it to skip over numeric columns -- lobs/raws and such
(easy to add a where datatype in ( 'VARCHAR2', 'CHAR' ) to the process!
......

is the snippet of text I have right after that link ;)

and the reason I have that there is cause, well, cause you'll get inconsistent datatypes

A reader, January 29, 2003 - 2:13 pm UTC

Wonderful!
Thanks!
-Nat

Sql to retrive a columns

Richard, January 29, 2003 - 9:20 pm UTC

Tom,

You are correct, I should have done more legwork on my own, I know your time is valuable.

By the way, your script works great now. It runs through about 9000 tables in about 7 minutes.

I was in a rush and missed the fix for the referenced snippet. I don't know how long the snippet takes to run ... I stopped it after an hour of doing the same search. I'll try to run it to completion on another day.

Thanks for the code and the fixes.

Richard

January 30, 2003 - 8:43 am UTC

I can show you a case where it takes hours for a single table ;)

it is a function of the "volume" of data -- not the number of tables....

Just an expert view

Anirudh, February 06, 2003 - 7:18 am UTC

Hi Tom,
Tried to develop the procedure for this and just wanted to have your expert comments
on this. Its quite slow, though. I have 20 tables in the schema, some of them are quite big
(million rows )
Anna itext_usr@SUCC1>
Anna itext_usr@SUCC1> CREATE OR REPLACE PROCEDURE proc_search_string(p_in_string VARCHAR2) IS
2 TYPE ref_cur IS REF CURSOR;
3 v_ref_cur ref_cur;
4 found_table user_tab_columns.table_name%TYPE;
5 found_col user_tab_columns.column_name%TYPE;
6 v_found VARCHAR2(50) := 'NOT FOUND';
7 v_select VARCHAR2(1000) ;
8 v_test VARCHAR2(1000);
9 v_col VARCHAR2(100);
10 v_instance_count NUMBER := 0;
11 BEGIN
12 -- We'll make use of user_objects first
13 -- dbms_output.put_line('1');
14 FOR i IN ( SELECT *
15 FROM user_tables)
16 LOOP
17 --Then user_tab_columns
18 --dbms_output.put_line('10');
19 FOR x IN ( SELECT column_name,data_type
20 FROM user_tab_columns
21 WHERE table_name = i.table_name
22 AND data_type IN ('VARCHAR2'))
23 LOOP
24
25 v_select := '';
26 v_col := x.column_name;
27 v_select := 'SELECT '||v_col||' FROM '||i.table_name||' WHERE '||
28 v_col||' LIKE '||CHR(39)||'%'||p_in_string||'%'||CHR(39);
29 OPEN v_ref_cur FOR v_select;
30 LOOP
31 EXIT WHEN v_ref_cur%NOTFOUND;
32 FETCH v_ref_cur INTO found_col;
33 IF v_ref_cur%FOUND THEN
34 v_instance_count := v_instance_count+1;
35 dbms_output.put_line(v_instance_count||'--> '||i.table_name||'.'||x.column_name);
36 END IF;
37 END LOOP;
38
39
40 --dbms_output.put_line(v_select);
41 END LOOP;
42 END LOOP;
43 --dbms_output.put_line('10000');
44
45 END;
46
47
48 /

Procedure created.

Anna itext_usr@SUCC1> exec proc_search_string('anirudh');
1--> ANI_TEST.VALUE
2--> LOCATION.MAYOR

PL/SQL procedure successfully completed.

February 06, 2003 - 8:46 am UTC

Like I said originally:

Be aware -- it isn't going to be *fast*. Another enhancement to it would be to
make it search for all columns in a table at the same time (instead of a full
scan per column).

you might look at the one I already coded above as it does a SINGLE full scan -- whereas yours does a full scan PER column

A reader, March 25, 2003 - 1:15 pm UTC

Hi Tom,

Is there any way to find table columns referenced in stored
Objects (procedure,function etc..)?

For eg. I have xyz procedure and it is refering
empno and ename column of table EMP.
I need sql statement that tell me

STORED PROCEDURE xyz IS REFERING column empno and ename
FROM TABLE emp

Thanks,

March 25, 2003 - 8:15 pm UTC

No, it sets up the dependency to the TABLE. at the column level, we don't track it.

OK

Basheer, December 24, 2003 - 1:04 am UTC

Hello Sir,
To describe the columns and datatypes we use the following
ways
1)describe <table_name>
2) we can issue a statement like the one
sql> select column_name,data_type from user_tab_columns
where table_name = <table_name>;
My question is
Do you any other ways to accomplish the same?
Thanks in advance.

December 24, 2003 - 10:01 am UTC

sure, in code you could "prepare" a "select * from <tablename>" and then use the programatic api's to describe the result set.

eg: dbms_sql, do a preparse and then a describe call, you'll have a table of records that describes each column -- its name, data type and so on.

OK

Kumar, April 10, 2004 - 12:59 am UTC

Dear Tom,
How to prevent columns of a table from being dropped?I think we can make use of triggers particularly the event
attribute function "ora_is_drop_column(column name)".Am I
right?But we have to use this for every column.Do you have
any other way?
Please do reply.
Bye!

April 10, 2004 - 11:42 am UTC

hows about not giving people who might inadvertently drop a column the privelege/capability to do so??????

yes, you could use an ALTER trigger -- but just not granting people the ability to do DDL to the table in the first place is the "right answer"

sankuls, February 01, 2011 - 12:47 am UTC

In the above example you have mentioned how to find the column name's which contains specific string?
Is there anyway to get the column data with corresponding column names?

Thanks,
sankuls

February 01, 2011 - 4:49 pm UTC

you would write a procedure, that would loop over all_tab_columns looking for that column name.

That would give you the owner and table name of every table containing that column name.

You could then build a dynamic sql statement - 'select column_name from owner.table' and dynamically execute it

Sankuls, February 02, 2011 - 1:12 am UTC

Sorry,what I meant was ,in the example which you had given(your example I have copied it once more below) we are able to find the column which contains the specific string.How can i find the corresponding data in the column?There could be instances where we are searching for the string which is part of the column data.How do I get the whole data as output?
Hope you are able understand.
=========================
create or replace
2 procedure find_string( p_str in varchar2 )
3 authid current_user
4 as
5 l_query long;
6 l_case long;
7 l_runquery boolean;
8 l_tname varchar2(30);
9 l_cname varchar2(30);
10 type rc is ref cursor;
11 l_cursor rc;
12 begin
13 dbms_application_info.set_client_info( '%' || upper(p_str) || '%' );
14
15 for x in (select * from user_tables )
16 loop
17 l_query := 'select distinct ''' || x.table_name || ''', $$
18 from ' || x.table_name || '
19 where ( 1=0 ';
20 l_runquery := FALSE;
21 l_case := NULL;
22 for y in ( select *
23 from user_tab_columns
24 where table_name = x.table_name
25 and data_type in ( 'VARCHAR2', 'CHAR' )
26 )
27 loop
28 l_runquery := TRUE;
29 l_query := l_query || ' or upper(' || y.column_name ||
30 ') like userenv(''client_info'') ';
31 l_case := l_case || '||'' ''|| case when upper(' || y.column_name ||
32 ') like userenv(''client_info'') then ''' ||
33 y.column_name || ''' else NULL end';
34 end loop;
35 if ( l_runquery )
36 then
37 l_query := replace( l_query, '$$', substr(l_case,8) ) || ')';
38 begin
39 open l_cursor for l_query;
40 loop
41 fetch l_cursor into l_tname, l_cname;
42 exit when l_cursor%notfound;
43 dbms_output.put_line
44 ( 'Found in ' || l_tname || '.' || l_cname );
45 end loop;
46 close l_cursor;
47 end;
48 end if;
49
50 end loop;
51 end;
52 /
=========================================
Thanks and Regards,
Sankuls

February 02, 2011 - 7:36 am UTC

as a programmer, you cannot take what is already given and modify it such that you could pass in a table name - and it would build a query such as:

select case when upper(c1) like p_str then c1 end c1,
       case when upper(c2) like p_str then c2 end c2,
       .....
  from p_table
 where upper(c1) like p_str or upper(c2) like p_str ...;

and print the results? You have the concept - you know it will involve dynamic SQL - you have the template - go for it!

If you do it and you think it would be useful for others - feel free to post...

Error while executing

James, August 01, 2014 - 12:54 pm UTC

Hey people , I got the following error while executing the above stored procedure find_string with the command
'exec find_string ('harry.fbdcn.com');' from my oracle sql developer.

Error starting at line 1 in command:
exec find_string ('harry.fbdcn.com');
Error report:
ORA-00907: missing right parenthesis
ORA-06512: at "fbdcn_users.FIND_STRING", line 38
ORA-06512: at line 1
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:

please help. I am a beginner.

- James

A simple SQL for not so complex search

Lalit Kumar B, January 06, 2015 - 8:13 am UTC

This would search for value 'KING' in 'SCOTT' schema.

SQL> conn scott@pdborcl/tiger
Connected.
SQL>
SQL> variable val varchar2(10)
SQL> exec :val := 'KING'

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
  2    SUBSTR (table_name, 1, 14) "Table",
  3    SUBSTR (column_name, 1, 14) "Column"
  4  FROM cols,
  5    TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  6    || column_name
  7    || ' from '
  8    || table_name
  9    || ' where upper('
 10    || column_name
 11    || ') like upper(''%'
 12    || :val
 13    || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
 14  ORDER BY "Table"
 15  /

Searchword  Table          Column
----------- -------------- --------------
KING        EMP            ENAME

SQL>

Regards,
Lalit Kumar B

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Problem with searching a value in multiple columns in a table

SQL TO RETRIVE A COLUMNS

SQL TO RETRIVE A COLUMNS

Sql to retrive a columns

Just an expert view

OK

OK

Error while executing

A simple SQL for not so complex search

More to Explore

PL/SQL demos

PL/SQL docs