Re: Select Select?

srea_at_uaex.edu wrote:
>
> I want to get a list of all tables and the number of records in those
> tables for just the tables that have records in them. Something
> like:
>
> select table_name,(select count(*) from table_name) nrecs
> from all_tab_comments having nrecs > 0 order by table_name;
>
> However, the count part is not valid here, and, even if it was valid,
> that second select would be looking for a table called "table_name"
> instead of using the value of table_name as the table to count.
>

Here's my script to produce nrecs and table_name:

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

This next script will produce the select statement for nrecs and column_name of all columns of all tables that belong to the user. Quite handy to check if all the columns are properly populated after loading/importing data but might take sometime to execute on large databases.

	select 'select count('||column_name||') nrecs,
	'''||table_name||'.'||column_name||''' from '||table_name||';'
	from user_tab_columns;

You can change user_tables and user_tab_columns to dba_tables and dba_tab_columns or all_tables depending on your need.

Noorazmi Abdul-Aziz
noorazmi_at_kuala-lumpur.geoquest.slb.com Received on Mon Aug 11 1997 - 00:00:00 CEST