List of Tables

List of Tables

Post by clc.. » Sun, 31 Dec 1899 09:00:00



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.

 
 
 

List of Tables

Post by Dave » Sun, 31 Dec 1899 09:00:00


select owner, table_name from all_tab_columns where column_name in
('column1','column2');

--
Dave A


> 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.


 
 
 

List of Tables

Post by Martin Haltmaye » Sun, 31 Dec 1899 09:00:00


select  --+ rule
        distinct
        t1.owner
        , t1.table_name
from
        all_tab_columns t1
        , all_tab_columns t2
where 1 = 1
and t1.owner = t2.owner
and t1.table_name = t2.table_name
and 'KCM_TRANS_NBR' = t1.column_name
and 'CUST_ID' = t2.column_name
order by 1, 2
/

Martin

 
 
 

List of Tables

Post by Mike Dwye » Sun, 31 Dec 1899 09:00:00


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'

> 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.

 
 
 

1. Tables list system table

Hello,

I just installed Sybase Adaptive Server on my Linux machine. I was looking
for a table containing all the table names (something like
ALL_TABLES in Oracle) and I couldn't find one. Can anyone tell me if there
is such a table in Sybase and if yes where to look for it?

Thanks,
Rafael.

2. OO4O Data Control: NOT READY FOR PRIME TIME

3. Tables list system table in Sybase

4. Data Report Parameter

5. (list of tables) table

6. onstat -h

7. Listing the tables in a database?

8. Sorting mdb data and VB5 data forms

9. List all table names ?

10. List of tables in database

11. List of Tables

12. Print out list of tables

13. Error when trying view list of tables in Enterprise Manager