Search for a string in schema

declare

cursor c1 is select column_name , table_name from user_tab_columns where

data_type = 'VARCHAR2';-- and table_name = 'TABLE_NAME' ;

vSQL varchar2(32676) := null;

vParam varchar2(100) := 'STING_YOU_WANT_TO_SEARCH';

vcnt number;

begin

for x in c1 loop

begin

vSQL := 'select count(*) from '||x.table_name||' where '||x.column_name||' like '||chr(39)||'%'||vparam||'%'||chr(39);

execute immediate vSQL into vcnt;

if vcnt > 0 then

dbms_output.put_line('Found in '||x.table_name||' '||x.column_name);

end if;

exception when others then

dbms_output.put_line('Problem in '||vSQL);

end;

end loop;

end;