How to search all columns of all tables in a database for a keyword

Dear Team,

i have an requirement that : i want to search all the columns of all the tables in the particular database based on the specific key word or an free text.

example :

table 1: columns data
empname sam
empid 01

table 2 columns data
deptname sam
departmentid 10

table 3 columns data
organization name sam
organization id 1

when i search for text " SAM"

it should search me from the entire database, all tables and columns of it and display the result

output : tablename cloumn value
table1 empname sam
table2 deptname sam
table3 organizationame sam

the example is just an sample not the real data .

please help me with sample code or any link related to it .


thanks in advance

 

are you using MS SQL Server 2005 or later versions? If yes, have you considered looking into the sys.tables catalog view...

 

actually i am using orcale 9i

 

sorry I don't use oracle at this time... have you tried looking into meta datas for oracle... SQL server has meta data information that are accessible through views and functions... I would make an educated guess that oracle may have a counter part to that, but, I don't know...