Home » SQL & PL/SQL » SQL & PL/SQL » Field name in table
Field name in table [message #235853] Tue, 08 May 2007 02:59 Go to next message
kdastageer
Messages: 28
Registered: January 2006
Location: Qatar
Junior Member
Dear All,

Please anyone give me the solution for the following question.

How to find out the same column name or field name is available in different tables in a single database by using query.

by
DAS
Re: Field name in table [message #235858 is a reply to message #235853] Tue, 08 May 2007 03:16 Go to previous messageGo to next message
vvlele
Messages: 1
Registered: May 2007
Location: India
Junior Member
vvl@ORA9IDEV> SELECT DISTINCT table_name
2 FROM all_tab_columns
3 WHERE column_name = 'EMPNO';

TABLE_NAME
------------------------------
EMP
Re: Field name in table [message #235874 is a reply to message #235853] Tue, 08 May 2007 04:15 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

SQL> select distinct table_name
2 from all_tab_cols
3 where COLUMN_NAME='EMPNO';

TABLE_NAME
------------------------------
EMP
TEST


Regards
Thangam
Re: Field name in table [message #235879 is a reply to message #235874] Tue, 08 May 2007 04:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, but what did your post add?
Re: Field name in table [message #235881 is a reply to message #235874] Tue, 08 May 2007 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Thangam

And your point is?

Regards
Michel
Re: Field name in table [message #235893 is a reply to message #235874] Tue, 08 May 2007 05:16 Go to previous messageGo to next message
kdastageer
Messages: 28
Registered: January 2006
Location: Qatar
Junior Member
Hi,

Thank you very much but i want to check in the views also.
Please help me.
Re: Field name in table [message #235895 is a reply to message #235881] Tue, 08 May 2007 05:41 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Yes Michel,
In point of fact in my database have EMP & TEST both tables having same columns, only Table name is Different thats why the result came two tables name EMP & TEST.

Regards
Thangam
Re: Field name in table [message #235949 is a reply to message #235853] Tue, 08 May 2007 08:44 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
I would change the query just a little. Don't use distinct, a table can have only one column with a particular name and the query given doesn't tell the user where the table is.


select owner,table_name
from all_tab_columns
where column_name = 'EMPNO';


Owner is the name of the owning schema AND ALL_TAB_COLUMNS also contain the columns for all views.

[Updated on: Tue, 08 May 2007 08:50]

Report message to a moderator

Previous Topic: what is the cause of bad bind variable error here?
Next Topic: Grant Edit Privilege of Package to Another user
Goto Forum:
  


Current Time: Sat Dec 10 09:07:48 CST 2016

Total time taken to generate the page: 0.05049 seconds