select owner, table_name from all_tab_columns where column_name in
('column1','column2');
--
Dave A
<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 Fri Oct 13 2000 - 18:36:52 CDT
