Re: List of Tables

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