SQL Query to find the table name which contains specific data

Requirement :  when you don't know what table or what column in your schema have the data.

This example, generates the table name / column name which contains the Data - 'samsung';

Thanks to Mohan for sending this Query. Worked fine for me hence posting .

######################

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\ag4063>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 12 18:22:49 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect sid_contenta
Enter password:
Connected.
SQL>

#####################################

spool outFile.txt

set serveroutput ON size 1000000

declare
srch_string varchar2(100) := 'samsung';
str varchar2(4000);
PROCEDURE get_column_list(tabname in varchar2) IS
begin
for c1 in(select column_name from user_tab_columns where table_name=tabname and data_type like '%CHAR%')
LOOP
str := str || 'upper('||c1.column_name||') like upper(''%'||srch_string||'%'') or ';
END LOOP;
str := rtrim(str, 'or ');
END get_column_list;
begin
for c2 in(select table_name from user_tables) LOOP
str := 'select * from '||c2.table_name||' where ';
get_column_list(c2.table_name);
dbms_output.put_line(str||';');
END LOOP;
end;
/

################################################
spool off
###############################################