Re: SQL Query Help

"Brett O'Neill" <bretto_at_swbell.net> wrote in message news:O62P5.833$_w6.105225_at_nnrp1.sbc.net...
> How would I write SQL queries to:
>
> a) find out how many tables are in my database?
> b) find out which table has the most fields?
>
>
> Any help greatly appreciated!
>
>
if you want ALL tables, which includes internal system tables, etc, then just

select count(1) from dba_tables;

if you want non-system tables then use

select count(1) from dba_tables
where owner not in ('SYS', 'SYSTEM');

for a specific schema (user) you can use either of

select count(1) from user_tables; --logged on as schema owner select count(1) from dba_tables where owner = 'SCHEMA_OWNER' --dba account

of course, you should replace 'SCHEMA_OWNER' with the actual owner name.

i expect that once you determine the appropriate query to use above, you can adapt it to the solution for the second problem below. just keep in mind there are two conventions for view names that are used: DBA_* for dba level access (everything) and USER_* for a specific schema (user). i will provide the following using the USER_* model.

select max(count(1)), table_name
from user_tab_columns
group by table_name; Received on Sun Nov 12 2000 - 00:52:53 CET