?
Solved

oracle sql query

Posted on 2003-03-27
10
Medium Priority
?
421 Views
Last Modified: 2008-03-06
hi guys,

Can we know that , a particular column is exist in which table of database.
E.g I am working in a database 'ABC' which have 100 tables, now I want to know the name of tables which have a column 'ENO'.
Please rep soon.
0
Comment
Question by:ajay_erdci
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
10 Comments
 

Expert Comment

by:azureshow
ID: 8222747
hi, you can use the sql as following to find the table which the column_name ='ENO'[select table_name from cols where column_name='ENO'].Hope you can make it soon.
/
0
 
LVL 1

Author Comment

by:ajay_erdci
ID: 8222764
Dear Azureshow,

I very sorry to say that there is a change in the question, I was trying to change it on the same time you make the reply.
That is not the table , basically its a public synonym which is assiend to each user.
Please consider the change and make the reply.

Sorry again

AJAY
0
 
LVL 1

Expert Comment

by:mona1974
ID: 8223242
COLS is a snonym for USER_TAB_COLUMNS. Try to select from ALL_TAB_COLUMNS.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 1

Author Comment

by:ajay_erdci
ID: 8223325
Dear Mona1974,

My user do't have the access to draw the data from tables, because he is no where conseren to table.
Give me the way by which we can know the synonym names in which that column has been used.

ajay
0
 
LVL 3

Expert Comment

by:oraelbis
ID: 8225173
As say mona1974 try select from ALL_TAB_COLUMNS such as:

select C.TABLE_NAME
  from ALL_TAB_COLUMNS C
 where C.COLUMN_NAME='ENO' and C.OWNER='ABC'
0
 
LVL 3

Expert Comment

by:oraelbis
ID: 8225281
You can grant this table directly to user or role.
Or such as for me, create simple view such as

create or replace view V_ALL_TAB_COLUMNS as
select C.TABLE_NAME, C.COLUMN_NAME, C.OWNER
 from SYS.ALL_TAB_COLUMNS C

and then grant this view to role or user such as:

GRANT SELECT ON V_ALL_TAB_COLUMNS TO FIN_ROLE;

granting privileges via ROLE are more simple, for many users.
0
 

Expert Comment

by:azureshow
ID: 8244240
SQL: select synonym_name from user_synonyms A,cols B
where A.table_name=B.table_name and B.column_name='ENO' and B.owner='ABC'

Sorry, I have been busy these days, and hope that you have solved it already. If not wish the SQL above will help you clear your question. PS:Because I can not understand your question well, I can only give you the answer from the other comments.
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10360649
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in approximately 4 days.  I would appreciate any comments by the experts that would help me in making a recommendation.

It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:
http://www.experts-exchange.com/help.jsp#hs5

walterecook
EE Cleanup Volunteer
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10392335
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ - no points refunded

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer
0
 

Accepted Solution

by:
amp072397 earned 0 total points
ID: 10426221
PAQed - no points refunded (of 150)

amp
Community Support Cleanup Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question