Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: List of Tables

Re: List of Tables

From: Mike Dwyer <dwyermj_at_co>
Date: Mon, 16 Oct 2000 07:44:13 -0600
Message-ID: <jIDG5.73$L32.31223@wdc-read-01.qwest.net>

select owner, table_name from all_tab_columns k, all_tab_columns c where k.owner = c.owner and k.table_name = c.table_name and k.column_name = 'KCM_TRANS_NBR'
and c.column_name = 'CUST_ID'

<clcope_at_my-deja.com> wrote in message news:8s7q8g$6vk$1_at_nnrp1.deja.com...
> I have a simple question Im sure. I just cant figure it out today. I
> want to list all tables in the database that contain two fields that I
> specify. For example, I want all tables that have the custid and
> transnbr field. Can someone help me on the sql for it? Below is an
> example of what I am trying to do, but the query takes so long, I am
> sure there is a better way.
>
> SELECT
> tbl.Owner,
> tbl.Table_Name
> FROM
> all_tables tbl,
> all_tab_columns fld
> WHERE tbl.Table_Name = fld.Table_Name
> AND (fld.Column_Name = 'KCM_TRANS_NBR'
> OR fld.Column_Name = 'CUST_ID')
> GROUP BY
> tbl.Owner,
> tbl.Table_Name
> HAVING Count(fld.Column_Name) > 1;
>
>
> Thanks in advance,
>
> Crystal L. Cope
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Oct 16 2000 - 08:44:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US