Home » SQL & PL/SQL » SQL & PL/SQL » I know column name but dont know from which table
I know column name but dont know from which table [message #184671] Thu, 27 July 2006 07:31 Go to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
Hi
I have a select statement with many columns. I dont know from which tables these columns are selected. Any way to find table name from which the columns are selected?

Thanks
Hari
Re: I know column name but dont know from which table [message #184675 is a reply to message #184671] Thu, 27 July 2006 07:39 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Refer the Data dictionaty view  "USER_TAB_COLS"  


Ex:

SELECT TABLE_NAME  FROM USER_TAB_COLS
WHERE COLUMN_NAME IN ('ENAME','EMPNO','DEPT')
GROUP BY TABLE_NAME 
HAVING COUNT(*)=3


Thumbs Up
Rajuvan.

[Updated on: Thu, 27 July 2006 07:48]

Report message to a moderator

Re: I know column name but dont know from which table [message #184676 is a reply to message #184671] Thu, 27 July 2006 07:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Presumably there are no table aliases in front of the columns.
Ie you select looks like
SELECT column_1
      ,column_2
      ,column_3

Rather than
SELECT a.column_1
      ,b.column_2
      ,a.column_3


If this is the case, you can copy and paste the list of column names and table names into this query (note, you'll need to wrap the column and table names in quotes and turn them into a comma seperated list):
select column_name,table_name
from   user_tab_columns
where  column_name in (<list of column names>)
and    table_name  in (<list of table names>);


If your clumns do have table alises, then you'll just have to match the alias with the table in the FROM clause.
Re: I know column name but dont know from which table [message #184694 is a reply to message #184671] Thu, 27 July 2006 08:41 Go to previous messageGo to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
I tried above 2 select statements. But they are displaying Zero rows. To make my question clear, I have few column names and there are 100s of tables in my schema. I dont know from which tables each column is coming. I need select statement to list table name and column names.

The above 2 statements displaying 0 rows.

Thanks
Hari
Re: I know column name but dont know from which table [message #184698 is a reply to message #184694] Thu, 27 July 2006 08:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Cut and paste the EXACT sql that you have just run.
icon6.gif  Re: I know column name but dont know from which table [message #185659 is a reply to message #184671] Wed, 02 August 2006 22:48 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
This is a script of my own if I want to know where the column resides. Maybe it would help.. Wink

set linesize 100
column data_type format  a10
column owner format  a10
column data_length format 9999
column table_name format  a30
column column_name format a30
select owner, table_name, column_name, data_type, data_length
from all_tab_columns
where column_name like '%&colname%' and owner in (select user from dual);


After you run the script, you will be prompted to give the name of the column. You can give the full name if you want to. http://planet.time.net.my/KLCC/freekey/smilies/thumbsup.gif
Re: I know column name but dont know from which table [message #185672 is a reply to message #184694] Thu, 03 August 2006 00:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you would change JRowBottom's query to select from all_tab_columns instead of user_tab_columns, I bet you WILL get rows back.
(My guess is you are not the owner of the tables)
Previous Topic: Re-Ordering a Table
Next Topic: how to travrse xml which come from webservice
Goto Forum:
  


Current Time: Mon Dec 05 02:29:42 CST 2016

Total time taken to generate the page: 0.13110 seconds