Home » SQL & PL/SQL » SQL & PL/SQL » Find table with column (Oracle 11g)
Find table with column [message #621267] Tue, 12 August 2014 04:34 Go to next message
keekee
Messages: 19
Registered: April 2010
Junior Member
How to find a table name with three specific column names in one table.
For instance, I have the column name "Member", "Message Icon" and "List Layout"
How to find the table name ?

Thank you for your help in advance.

Re: Find table with column [message #621268 is a reply to message #621267] Tue, 12 August 2014 04:36 Go to previous messageGo to next message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
select table_name
from user_tab_columns
where column_name = 'MEMBER'
Re: Find table with column [message #621273 is a reply to message #621267] Tue, 12 August 2014 05:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2405
Registered: May 2013
Location: World Wide on the Web
Senior Member
keekee wrote on Tue, 12 August 2014 15:04
How to find a table name with three specific column names in one table.
For instance, I have the column name "Member", "Message Icon" and "List Layout"
How to find the table name ?


If you have too many tables to search having a column_name, then you could extend Littlefoot's solution to look for the table_name you think will have the three specific columns :

SELECT table_name 
FROM   user_tab_columns 
WHERE  column_name = 'MEMBER' 
INTERSECT 
SELECT table_name 
FROM   user_tab_columns 
WHERE  column_name = 'MESSAGE_ICON' 
INTERSECT 
SELECT table_name 
FROM   user_tab_columns 
WHERE  column_name = 'LIST_LAYOUT'; 
Re: Find table with column [message #621286 is a reply to message #621273] Tue, 12 August 2014 07:03 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
Lalit Kumar B wrote on Tue, 12 August 2014 06:56
you could extend Littlefoot's solution to look for the table_name you think will have the three specific columns :



But not in such inefficient way:

SELECT  table_name 
  FROM  user_tab_columns 
  WHERE  column_name = ANY('MEMBER','MESSAGE_ICON','LIST_LAYOUT')
  GROUP BY table_name
  HAVING COUNT(*) = 3
/


For example:

SQL> SELECT  table_name 
  2    FROM  user_tab_columns 
  3    WHERE  column_name = ANY('EMPNO','SAL','COMM')
  4    GROUP BY table_name
  5    HAVING COUNT(*) = 3
  6  /

TABLE_NAME
------------------------------
EMP1
EMP
EMP_TST
ERR$_EMP_TST
EMP2
EMP_TARGET

6 rows selected.

SQL> 


SY.
Re: Find table with column [message #621292 is a reply to message #621286] Tue, 12 August 2014 07:43 Go to previous messageGo to next message
knw15pwr
Messages: 130
Registered: March 2010
Senior Member
Hi Solomon,
based on the docs, the =ANY is equivalent to the IN operator. Is there any specific case where it should be used or shouldn't be used ?
Or can this be ignored completely.
I did read the below article and related articles at the bottom of the page, but couldn't understand if these operators had any specific advantage/disadvantage.
http://www.oracle-base.com/articles/misc/all-any-some-comparison-conditions-in-sql.php
Re: Find table with column [message #621293 is a reply to message #621292] Tue, 12 August 2014 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Is there any specific case where it should be used or shouldn't be used ?
Or can this be ignored completely.


The later one.

SQL> @xpl3p

  2  select * from dual where dummy in ('A','B','C');

Explained.

SQL> @xpl3
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |     2   (0)|
|*  1 |  TABLE ACCESS FULL| DUAL |      1 |     2   (0)|
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DUMMY"='A' OR "DUMMY"='B' OR "DUMMY"='C')

SQL> @xpl3p

  2  select * from dual where dummy = any ('A','B','C');

Explained.

SQL> @xpl3
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |     2   (0)|
|*  1 |  TABLE ACCESS FULL| DUAL |      1 |     2   (0)|
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DUMMY"='A' OR "DUMMY"='B' OR "DUMMY"='C')

Both are converted to OR. Smile

Re: Find table with column [message #621301 is a reply to message #621293] Tue, 12 August 2014 08:17 Go to previous messageGo to next message
knw15pwr
Messages: 130
Registered: March 2010
Senior Member
Perfect. Thanks. I wonder why oracle kept this operator if its redundant.
Re: Find table with column [message #621302 is a reply to message #621301] Tue, 12 August 2014 08:21 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
> I wonder why oracle kept this operator if its redundant.
Oracle does not decide what is in SQL; but Internationals Standards Organization
http://en.wikipedia.org/wiki/Iso
Re: Find table with column [message #621304 is a reply to message #621302] Tue, 12 August 2014 08:53 Go to previous messageGo to next message
knw15pwr
Messages: 130
Registered: March 2010
Senior Member
Point taken. However, don't you feel it would be more useful if you would have pointed out to the link with ISO standards on SQL rather than a wikipedia listing on ISO ? Anyway, thanks for the clarification.

P.S.- Sorry Keekee for hijacking your thread, i hope your problem was solved.
Re: Find table with column [message #621305 is a reply to message #621304] Tue, 12 August 2014 08:56 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
knw15pwr wrote on Tue, 12 August 2014 06:53
Point taken. However, don't you feel it would be more useful if you would have pointed out to the link with ISO standards on SQL rather than a wikipedia listing on ISO ?



nothing is impossible for the person who does not have to do it themselves.
Why did YOU not provide the URL?
Re: Find table with column [message #621307 is a reply to message #621304] Tue, 12 August 2014 09:01 Go to previous messageGo to next message
Bill B
Messages: 1108
Registered: December 2004
Senior Member
The solutions given will return table names that contain any of the three column names. The following select will return only the table name containing all the columns.

select a.table_name,a.owner
from all_tab_columns a,all_tab_columns b,all_tab_columns c
where a.owner = b.owner
and a.table_name = b.table_name
and  a.owner = c.owner
and a.table_name = c.table_name
and a.column_name = 'MEMBER'
AND B.COLUMN_NAME = 'MESSAGE_ICON'
AND C.COLUMN_NAME = 'LIST_LAYOUT';

[Updated on: Tue, 12 August 2014 09:02]

Report message to a moderator

Re: Find table with column [message #621309 is a reply to message #621307] Tue, 12 August 2014 09:07 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
Bill B wrote on Tue, 12 August 2014 10:01
The solutions given will return table names that contain any of the three column names.


???

SQL> create table tbl1(member number);

Table created.

SQL> create table tbl2(member number,message_icon number);

Table created.

SQL> create table tbl3(member number,message_icon number,list_layout number);

Table created.

SQL> SELECT  table_name 
  2    FROM  user_tab_columns 
  3    WHERE  column_name = ANY('MEMBER','MESSAGE_ICON','LIST_LAYOUT')
  4    GROUP BY table_name
  5    HAVING COUNT(*) = 3
  6  /

TABLE_NAME
------------------------------
TBL3

SQL> 


SY.
Re: Find table with column [message #621311 is a reply to message #621307] Tue, 12 August 2014 09:08 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2405
Registered: May 2013
Location: World Wide on the Web
Senior Member
Quote:
The solutions given will return table names that contain any of the three column names. The following select will return only the table name containing all the columns.


Bill, what if more than one table have the three columns in common? Your query needs to keep adding the join and filter.
Re: Find table with column [message #621313 is a reply to message #621309] Tue, 12 August 2014 09:10 Go to previous messageGo to next message
Bill B
Messages: 1108
Registered: December 2004
Senior Member
sorry, missed the count(*) = 3. Your totally right
Re: Find table with column [message #621318 is a reply to message #621311] Tue, 12 August 2014 09:29 Go to previous message
cookiemonster
Messages: 10981
Registered: September 2008
Location: Rainy Manchester
Senior Member
Lalit Kumar B wrote on Tue, 12 August 2014 15:08
Quote:
The solutions given will return table names that contain any of the three column names. The following select will return only the table name containing all the columns.


Bill, what if more than one table have the three columns in common? Your query needs to keep adding the join and filter.


No it works as it is.
Previous Topic: read hebrew from file
Next Topic: issue inserting multiple lines into one row
Goto Forum:
  


Current Time: Tue Sep 30 18:13:30 CDT 2014

Total time taken to generate the page: 0.09856 seconds