Home » SQL & PL/SQL » SQL & PL/SQL » how to search a value in all tables
how to search a value in all tables [message #238273] Thu, 17 May 2007 04:59 Go to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
Hi All,
How to search for a value in all columns in all tables in SQL. I have searched the forum, but didnt get the result. Request you all to help me out.

Thanks in advance.
Re: how to search a value in all tables [message #238279 is a reply to message #238273] Thu, 17 May 2007 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
where col1=val or col2=val or...
for all tables

This is only SQL way.

Or dump your database in flat file and use grep.

Regards
Michel
Re: how to search a value in all tables [message #238283 is a reply to message #238273] Thu, 17 May 2007 05:31 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
This AskTom thread has a neat procedure for this kind of search.



Re: how to search a value in all tables [message #238323 is a reply to message #238283] Thu, 17 May 2007 07:18 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
I write a simple procedure using dynamic SQL and search that way.
Re: how to search a value in all tables [message #238326 is a reply to message #238323] Thu, 17 May 2007 07:20 Go to previous messageGo to next message
cutsmartprem
Messages: 62
Registered: November 2006
Member
Thanks. Waiting for the reply..
Re: how to search a value in all tables [message #238340 is a reply to message #238326] Thu, 17 May 2007 08:04 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
This is what I use to search for string values. Modify the script each time the search value changes or use as subtitution parameter.

declare
type table_name_tab_type is table of tabs.TABLE_NAME%type index by binary_integer;
type col_name_tab_type is table of cols.COLUMN_NAME%type index by binary_integer;

table_name_tab table_name_tab_type;
col_name_tab col_name_tab_type;

looper binary_integer;
looper1 binary_integer;
sql_stmt varchar2(1024);
count_value pls_integer;

begin

select table_name
bulk collect into table_name_tab
from tabs;

if table_name_tab.count >0 then

for looper in table_name_tab.first..table_name_tab.last loop
col_name_tab.delete;
select column_name
bulk collect into col_name_tab
from cols
where table_name = table_name_tab(looper)
and data_type in ('VARCHAR2','CHAR');
if col_name_tab.count > 0 then
for looper1 in col_name_tab.first..col_name_tab.last loop
sql_stmt := 'select count (*) from ' ||
table_name_tab(looper) ||
' where upper(' ||
col_name_tab(looper1) ||
') like ''%BARCODE%''';
-- ^^^^^^^^
-- search value
execute immediate sql_stmt into count_value;
if count_value > 0 then
dbms_output.put_line ( 'Table: ' ||
table_name_tab(looper) ||
' Col Name: ' ||
col_name_tab(looper1) ||
' has the search for value');
end if;
end loop;
end if;
end loop;
end if;

end;
Re: how to search a value in all tables [message #238422 is a reply to message #238340] Thu, 17 May 2007 23:55 Go to previous message
cutsmartprem
Messages: 62
Registered: November 2006
Member
the procedure worked fine.

i got the correct output which i expected.

thanks a lot!!
Previous Topic: Group by
Next Topic: Update Statement
Goto Forum:
  


Current Time: Sat Dec 03 20:12:02 CST 2016

Total time taken to generate the page: 0.12063 seconds