-
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';
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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!
-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|