| Find table with column [message #621267] |
Tue, 12 August 2014 04:34  |
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 #621273 is a reply to message #621267] |
Tue, 12 August 2014 05:56  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
keekee wrote on Tue, 12 August 2014 15:04How 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 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Lalit Kumar B wrote on Tue, 12 August 2014 06:56you 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 #621304 is a reply to message #621302] |
Tue, 12 August 2014 08:53 |
knw15pwr
Messages: 134
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 #621307 is a reply to message #621304] |
Tue, 12 August 2014 09:01 |
Bill B
Messages: 1971
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 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Bill B wrote on Tue, 12 August 2014 10:01The 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 |
Lalit Kumar B
Messages: 3174
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 #621318 is a reply to message #621311] |
Tue, 12 August 2014 09:29 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Lalit Kumar B wrote on Tue, 12 August 2014 15:08Quote: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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
