Searching for table names and columns names in the database

Hi all.. i am new to oracle database and try to retrieve all the column names and tables names for a particular number (column value from one table). I am using following two different queries but getting no results and no errors. any help??

query1 :
select table_name, column_name from
(select rownum,table_name, column_name, dbms_xmlgen.getxml
('select 1 from "'||table_name||'" where "'||column_name||'"=&number') x
from user_tab_columns where data_type='NUMBER') where length(x)!=0;

query2:

select table_name,
column_name
from( select table_name,
column_name,
to_number(
extractvalue(
xmltype(
dbms_xmlgen.getxml(
'select count(*) c from ' || table_name ||
' where NUMBER(' || column_name || ') = ''589773'''
)
),
'ROWSET/ROW/C'
)
) cnt
from (select utc.*, rownum
from user_tab_columns utc
where data_type in ('NUMBER') ) )
where cnt >= 0

please let me know if u have any questions

Thanks!

In addition to this, i am using 11g client for 10g oracle database.

Let me check my understanding of your business requirements, is the idea to get a list of the tables that include a particular column name?

Like... select table_name, column_name from user_tab_columns where column_name = 'whatever-column-name';

Thanks PAVB for replying back .

No i dont know column names ..I am looking for tables names and column names that has a particular value in it. for example the reuslt shld look like this

Table column value
table1 column1 abc
table 4 column10 abc
table6 column3 abc

does it make sense?

thanks!

Quote:

---

Originally Posted by CornerQuery

Thanks PAVB for replying back .

No i dont know column names ..I am looking for tables names and column names that has a particular value in it. for example the reuslt shld look like this

Table column value
table1 column1 abc
table 4 column10 abc
table6 column3 abc

does it make sense?

thanks!

---

do you mean... a particular value as part of the table_name and/or column_name OR a particular value stored in a particular column?

think he means search every column in every table

http://asktom.oracle.com/pls/asktom/...42426083757635 has a function to do that

yeah ..search for column names and table names in the database for a same value stored in that column.

Quote:

---

Originally Posted by CornerQuery

yeah ..search for column names and table names in the database for a same value stored in that column.

---

That's an unusual requirement, please follow Davey's lead.