Data Search String

Below script will help you to search a specific string of data and list out all the tables and column names which has this data string...

create table FIND_SEARCH_STRING (TABLE_NAME VARCHAR2(35) , COLUMN_NAME VARCHAR2(35),MAX_COUNT NUMBER(30,0), DATA_TYPE VARCHAR2(35 CHAR));

set linesize 200
set pagesize 500
col TABLE_NAME for a30
col COLUMN_NAME for a30
delete from FIND_SEARCH_STRING;
commit;
DECLARE
sql_stmt varchar2(256);
tab_count varchar2(100);
cursor c_f is SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS where TABLE_NAME not like 'FIND_SEARCH_STRING%' and data_type = 'VARCHAR2' and table_name like 'Table%';
  v_a USER_TAB_COLUMNS.TABLE_NAME%type;
  v_b USER_TAB_COLUMNS.COLUMN_NAME%type;
  v_c USER_TAB_COLUMNS.DATA_TYPE%type;

BEGIN
open c_f;
  loop
    fetch c_f into v_a, v_b , v_c;
    exit when c_f%notfound;

sql_stmt :='SELECT count(*) from '|| v_a || ' where '|| v_b ||' like :1';

----change below----
execute immediate sql_stmt into tab_count using 'ABC%';


---    execute immediate 'SELECT max(v_b) into tab_count from v_a where current of c_f';
 insert into FIND_SEARCH_STRING values (v_a,v_b,tab_count,v_c);
commit;
  END LOOP;
END;
/
select * from FIND_SEARCH_STRING where MAX_COUNT > 0;

With Owner:

create table FIND_SEARCH_TEST (OWNER VARCHAR(20) , TABLE_NAME VARCHAR2(35) , COLUMN_NAME VARCHAR2(35),MAX_COUNT NUMBER(30,0), DATA_TYPE VARCHAR2(35 CHAR));

set serveroutput on

set linesize 200

set pagesize 500

col TABLE_NAME for a30

col COLUMN_NAME for a30

delete from FIND_SEARCH_STRING;

drop table FIND_SEARCH_TEST;

commit;

DECLARE

sql_stmt varchar2(256);

tab_count varchar2(100);

cursor c_f is SELECT OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM DBA_TAB_COLUMNS where TABLE_NAME not like 'FIND_SEARCH_TEST%' 

and data_type = 'VARCHAR2' 

and owner like 'WM_%'

and column_name in('USER_NAME','LOGIN_USER_ID');

  v_d DBA_TAB_COLUMNS.OWNER%type;

  v_a DBA_TAB_COLUMNS.TABLE_NAME%type;

  v_b DBA_TAB_COLUMNS.COLUMN_NAME%type;

  v_c DBA_TAB_COLUMNS.DATA_TYPE%type;

  

BEGIN

open c_f;

  loop

    fetch c_f into v_d, v_a, v_b , v_c;

    exit when c_f%notfound;

sql_stmt :='SELECT count(*) from '|| v_d ||'.'|| v_a ||' where '|| v_b ||' like :1';

----change below----

execute immediate sql_stmt into tab_count using '%ABC%';

commit;

DBMS_OUTPUT.PUT_LINE('SELECT count(*) from '|| v_d ||'.'|| v_a ||' where '|| v_b ||' like ''%ABC%''; ');

---    execute immediate 'SELECT max(v_b) into tab_count from v_a where current of c_f';

 insert into FIND_SEARCH_TEST values (v_d,v_a,v_b,tab_count,v_c);

commit;

  END LOOP;

END;

/