Find_string_inthe_schema

This particular code is used to find out the given value is present in which table :

create table table_row_count ( name varchar2(100));

declare
l_count number:=0;
v_sql   varchar2(4000);
v_string varchar2(100):='Sample101';
begin
for i in ( select * from user_tab_cols  where data_type in ('CHAR','VARCHAR2') and table_name not like '%BIN$%')
loop
v_sql := ' select distinct 1 from '||i.table_name  || ' where '||i.column_name||' like '
          ||''''||'%vinodcust/vinodcustuser3/outback21%'||'''';
  dbms_output.put_line ( v_sql);
begin
 execute immediate  v_sql into l_count;
 insert into table_row_count values ( i.table_name);
exception
when no_data_found then
 null;
 end;
end loop;
end;
/

select * from table_row_count;

drop table table_row_count;