Re: Select Count off tables.

,Grahame Pearson <gpearson_at_metz.une.edu.au> wrote:
: How can I do this.
 

: For example
 

: If you firstly
: select distinct table_name from all_tab_columns
: where owner = 'SYS'
 

: You will return x number of rows
 

: Including
: USER_TABLES
: USER_SOURCE
 
: I want to have a query (PLSQL or SQL) where you can return the above record
: (most likely into a cursor) and the have another sql that does the following
 

: Select count(*), first_table_returned
: from first_table_returned;
 

: then select count(*), second_table_name_returned
: from second_table_name_retuned;
 

: etc, etc
 

: So that you would get the following output (from the above example)
 

: 32,USER_TABLES
: 343,USER_SOURCE
 
: etc, etc
 

: Any Ideas?

Here's an SQL script used to do something similar that might help you.

set pause off
set headi off
set feedback off
set pagesize 5000
set trimspool on

spool &&1..sql

select 'select count (*), ''' || table_name || ''' from ' || table_name || ';' from user_tables
order by table_name
;

spool off

spool &&1
@&&1..sql
spool off

Helen Received on Fri Jul 27 2001 - 03:36:19 CDT