Search string in database

Oracle find string or number in the database

search string
find text in database

set serveroutput on size 1000000

accept string char prompt "Enter Char String to search for:"

accept owner_name prompt "Enter Owner Name:"

set term off

set verify off

set lines 80

set feedback off

spool find_string.tmp

prompt spool find_string.out

prompt set feedback off

prompt set pages 1000

prompt set lines 132

prompt set wrap on

prompt break on found_in skip 1

prompt col found_in format a55 heading "Found In .."

prompt col string format a70 heading "String Found"

-- prompt col row_id format 99999999999999999999

prompt set term on

prompt 

declare

cursor varchar_tables_cur IS

select owner

, table_name

, column_name

from dba_tab_columns

where owner != 'SYS'

and owner != 'SYSTEM'

and owner = upper('&OWNER_NAME')

and data_type in ( 'VARCHAR2','VARCHAR','CHAR');

begin

for v_t_rec in varchar_tables_cur loop

dbms_output.put_line('--');

-- dbms_output.put_line('prompt '||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_name||'');

dbms_output.put('select '''||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');

dbms_output.new_line;

dbms_output.put(''||v_t_rec.column_name||' String ');

dbms_output.new_line;

-- dbms_output.put_line('rowid Row_id ');

dbms_output.put_line('from '||v_t_rec.owner||'.'||v_t_rec.table_name||'');

dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'&string'||chr(39)||'');

dbms_output.put_line('/'); 

end loop;

end;

/

prompt spool off

prompt prompt ====> created file find_string.out for your review

spool off

@find_string.tmp

===

-- set term off

set verify off

set lines 80

set feedback off

set serveroutput on

spool find_string.tmp

prompt spool find_string.out

prompt set feedback off

prompt set pages 1000

prompt set lines 132

prompt set wrap on

prompt break on found_in skip 1

prompt col found_in format a55 heading "Found In .."

prompt col string format a70 heading "String Found"

-- prompt col row_id format 99999999999999999999

prompt set term on

prompt

declare

cursor varchar_tables_cur IS

select table_name,

column_name

from user_tab_columns a, user_objects b

where data_type in ( 'VARCHAR2','VARCHAR','CHAR')

and object_name=table_name

and object_type='TABLE' ;

-- and a.OWNER not in ('SYS','SYSTEM') ;

begin

for v_t_rec in varchar_tables_cur loop

dbms_output.put_line('--');

-- dbms_output.put_line('prompt '||v_t_rec.table_name||'.'||v_t_rec.column_name||'');

dbms_output.put('select distinct'''||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');

dbms_output.new_line;

dbms_output.put(''||v_t_rec.column_name||' String ');

dbms_output.new_line;

-- dbms_output.put_line('rowid Row_id ');

dbms_output.put_line('from '||v_t_rec.table_name||'');

dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'&string'||chr(39)||'');

dbms_output.put_line('/');

end loop;

end;

/

prompt spool off

prompt prompt ====> created file find_string.out for your review

spool off

@find_string.tmp

===

Oracle search string from a database 

-- set term off

set verify off

set lines 80

set feedback off

set serveroutput on

spool find_string.tmp

prompt spool find_string.out

prompt set feedback off

prompt set pages 1000

prompt set lines 132

prompt set wrap on

prompt break on found_in skip 1

prompt col found_in format a55 heading "Found In .."

prompt col string format a70 heading "String Found"

-- prompt col row_id format 99999999999999999999

prompt set term on

prompt

declare

cursor varchar_tables_cur IS

select table_name,a.owner owner, column_name

-- from user_tab_columns a, user_objects b

from dba_tab_columns a, dba_objects b

where data_type in ( 'VARCHAR2','VARCHAR','CHAR')

and object_name=table_name

and object_type='TABLE'

and a.OWNER not in ('SYS','SYSTEM') ;

begin

for v_t_rec in varchar_tables_cur loop

dbms_output.put_line('--');

-- dbms_output.put_line('prompt '||v_t_rec.table_name||'.'||v_t_rec.column_name||'');

dbms_output.put('select distinct'''||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');

dbms_output.new_line;

dbms_output.put(''||v_t_rec.column_name||' String ');

dbms_output.new_line;

-- dbms_output.put_line('rowid Row_id ');

dbms_output.put_line('from '||v_t_rec.owner||'.'||v_t_rec.table_name||'');

dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'&string'||chr(39)||'');

dbms_output.put_line('/');

end loop;

end;

/

prompt spool off

prompt prompt ====> created file find_string.out for your review

spool off

@find_string.tmp

====

set serveroutput on size 1000000

accept string char prompt "Enter Char String to search for:"

accept owner_name prompt "Enter Owner Name:"

set term off

set verify off

set lines 80

set feedback off

spool find_string.tmp

prompt spool c:\temp\find_string.out

prompt set feedback off

prompt set pages 1000

prompt set lines 132

prompt set wrap on

prompt break on found_in skip 1

prompt col found_in format a55 heading "Found In

.."

p rompt col string format a70 heading "String Found"

-- prompt col row_id format 99999999999999999999

prompt set term on

prompt

declare

cursor varchar_tables_cur IS

select owner

, table_name

, column_name

from dba_tab_columns

where owner != 'SYS'

and owner != 'SYSTEM'

and owner = '&OWNER_NAME'

and data_type in ( 'VARCHAR2','VARCHAR','CHAR');

begin

for v_t_rec in varchar_tables_cur loop

dbms_output.put_line('--');

-- dbms_output.put_line('prompt '||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_n ame||'');

dbms_output.put('select '''||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');

dbms_output.new_line;

dbms_output.put(''||v_t_rec.column_name||' String ');

dbms_output.new_line;

-- dbms_output.put_line('rowid Row_id ');

dbms_output.put_line('from '||v_t_rec.owner||'.'||v_t_rec.table_name||'');

dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'%&string%'||chr(39)||'');

dbms_output.put_line('/');

end loop;

end;

/

prompt spool off

prompt prompt ====> created file find_string.out for your review

spool off

@find_string.tmp