List of Tables

From: <clcope_at_my-deja.com>
Date: Fri, 13 Oct 2000 20:11:03 GMT
Message-ID: <8s7q8g$6vk$1@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 - 15:11:03 CDT