[prev in list] [next in list] [prev in thread] [next in thread]
List: odtug-sqlplus-l
Subject: RE: Simple question on the all_tab_cols table
From: "John Roberts" <john.roberts () 627 ! co ! uk>
Date: 2004-10-19 18:25:54
Message-ID: F001.005EDE0A.20041019112026 () fatcity ! com
[Download RAW message or body]
or (less elegantly...)
select table_name from all_tab_columns where column_name='COL_1'
intersect
select table_name from all_tab_columns where column_name='COL_2'
Regards
John Roberts
-----Original Message-----
Asensio, Andres (CCL)
Sent: 19 October 2004 20:06
To: Multiple recipients of list ODTUG-SQLPLUS-L
select table_name, count(1) count
from all_tab_cols
where column_name in ('......')
group by table_name
having count(1) = 5
-----Original Message-----
Bruce.Moore@conectiv.com
Sent: Tuesday, October 19, 2004 2:50 PM
To: Multiple recipients of list ODTUG-SQLPLUS-L
I want to find a table using the all_tab_cols table. I want it to return to
me the name of any table that contains a list of like 5 column names. So I
want it to say give me the name of the table that has column1 and column2
and column3 and column4 and column5 in it
If I do a select table_name from all_tab_cols where column_name = 'COLUMN1'
and column_name='COLUMN2' and...
I get no rows returned
If I try to union a bunch of selects and do a group by, I get more than
expected.
Can I do this? Or is there another table/view I should be using?
This Email message and any attachment may contain information that is
proprietary, legally privileged, confidential and/or subject to copyright
belonging to Pepco Holdings, Inc. or its affiliates ("PHI"). This Email is
intended solely for the use of the person(s) to which it is addressed. If
you are not an intended recipient, or the employee or agent responsible for
delivery of this Email to the intended recipient(s), you are hereby notified
that any dissemination, distribution or copying of this Email is strictly
prohibited. If you have received this message in error, please immediately
notify the sender and permanently delete this Email and any copies. PHI
policies expressly prohibit employees from making defamatory or offensive
statements and infringing any copyright or any other legal right by Email
communication. PHI will not accept any liability in respect of such
communications. Desktop 2005 - You're Virtually There! February 15-17, 2005
Visit http://www.odtug.com for details. Computer + internet required.
Keynote Speakers: Bill Inmon, Sohaib Abbasi and Thomas Kurian. -- Author:
INET: Bruce.Moore@conectiv.com Fat City Hosting, San Diego, California --
http://www.fatcity.com
--------------------------------------------------------------------- To
REMOVE yourself from this mailing list, send an E-Mail message to:
ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message
BODY, include a line containing: UNSUB ODTUG-SQLPLUS-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
[Attachment #3 (text/html)]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2900.2523" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=666241518-19102004><FONT face=Arial color=#0000ff size=2>or
(less elegantly...)<BR><BR></FONT></SPAN><SPAN class=666241518-19102004><FONT
color=#0000f0><FONT size=2>select</FONT></FONT><FONT size=2> table_name <FONT
color=#0000f0>from</FONT> all_tab_columns <FONT color=#0000f0>where</FONT>
column_name<FONT color=#0000f0>=<SPAN
class=666241518-19102004>'COL_1'<BR></SPAN></FONT></FONT><FONT
color=#0000f0><FONT size=2>intersect<BR></FONT><FONT
size=2>select</FONT></FONT><FONT size=2> table_name <FONT
color=#0000f0>from</FONT> all_tab_columns <FONT color=#0000f0>where</FONT>
column_name<FONT color=#0000f0>=<SPAN
class=666241518-19102004>'COL_2'</SPAN></FONT></FONT></DIV></SPAN>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<P><FONT size=2>Regards<BR><BR>John Roberts<BR></FONT></P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
ml-errors@fatcity.com [mailto:ml-errors@fatcity.com] <B>On Behalf Of
</B>Asensio, Andres (CCL)<BR><B>Sent:</B> 19 October 2004 20:06<BR><B>To:</B>
Multiple recipients of list ODTUG-SQLPLUS-L<BR><B>Subject:</B> RE: Simple
question on the all_tab_cols table<BR><BR></FONT></DIV>
<DIV><SPAN class=591250118-19102004><FONT face=Arial color=#0000ff
size=2>select table_name, count(1) count</FONT></SPAN></DIV>
<DIV><SPAN class=591250118-19102004><FONT face=Arial color=#0000ff size=2>from
all_tab_cols</FONT></SPAN></DIV>
<DIV><SPAN class=591250118-19102004><FONT face=Arial color=#0000ff
size=2>where column_name in ('......')</FONT></SPAN></DIV>
<DIV><SPAN class=591250118-19102004><FONT face=Arial color=#0000ff
size=2>group by table_name</FONT></SPAN></DIV>
<DIV><SPAN class=591250118-19102004><FONT face=Arial color=#0000ff
size=2>having count(1) = 5</FONT></SPAN></DIV>
<DIV><SPAN class=591250118-19102004></SPAN> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> ml-errors@fatcity.com
[mailto:ml-errors@fatcity.com]<B>On Behalf Of
</B>Bruce.Moore@conectiv.com<BR><B>Sent:</B> Tuesday, October 19, 2004 2:50
PM<BR><B>To:</B> Multiple recipients of list
ODTUG-SQLPLUS-L<BR><B>Subject:</B> Simple question on the all_tab_cols
table<BR><BR></FONT></DIV>
<P>I want to find a table using the all_tab_cols table. I want it to return
to me the name of any table that contains a list of like 5 column names. So
I want it to say give me the name of the table that has column1 and column2
and column3 and column4 and column5 in it<BR>If I do a select table_name
from all_tab_cols where column_name = 'COLUMN1' and column_name='COLUMN2'
and...<BR>I get no rows returned<BR>If I try to union a bunch of selects and
do a group by, I get more than expected.<BR>Can I do this? Or is there
another table/view I should be using?<BR><BR><BR><I><FONT color=#808080
size=2>This Email message and any attachment may contain information that is
proprietary, legally privileged, confidential and/or subject to copyright
belonging to Pepco Holdings, Inc. or its affiliates ("PHI"). This Email is
intended solely for the use of the person(s) to which it is addressed. If
you are not an intended recipient, or the employee or agent responsible for
delivery of this Email to the intended recipient(s), you are hereby notified
that any dissemination, distribution or copying of this Email is strictly
prohibited. If you have received this message in error, please immediately
notify the sender and permanently delete this Email and any copies. PHI
policies expressly prohibit employees from making defamatory or offensive
statements and infringing any copyright or any other legal right by Email
communication. PHI will not accept any liability in respect of such
communications.</FONT></I> Desktop 2005 - You're Virtually There! February
15-17, 2005 Visit http://www.odtug.com for details. Computer + internet
required. Keynote Speakers: Bill Inmon, Sohaib Abbasi and Thomas Kurian. --
Author: INET: Bruce.Moore@conectiv.com Fat City Hosting, San Diego,
California -- http://www.fatcity.com
--------------------------------------------------------------------- To
REMOVE yourself from this mailing list, send an E-Mail message to:
ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message
BODY, include a line containing: UNSUB ODTUG-SQLPLUS-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
</P></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>
Desktop 2005 - You're Virtually There! February 15-17, 2005
Visit http://www.odtug.com for details. Computer + internet required.
Keynote Speakers: Bill Inmon, Sohaib Abbasi and Thomas Kurian.
--
Author: John Roberts
INET: john.roberts@627.co.uk
Fat City Hosting, San Diego, California -- http://www.fatcity.com
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ODTUG-SQLPLUS-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic