SV: Empty Table

  • From: Jesper Haure Norrevang <jhn.aida@xxxxxx>
  • To: jacintakean@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 24 Jan 2005 14:05:54 +0100

Jacinta,

> I need some help here. I need to get a report of all
> the table that are empty on a schema.

A quick way  to determine whether a table
is empty, regardless whether there are milions
of rows in the table or not:

select count(*) from some_table where rownum < 2;

I have played a little with SQL*Plus. It is not
nice, but it works.

set lines 132
set heading off
set pages 0
set feedback off
spool work.sql
select 
  'select ''' || table_name || ''' from ' || table_name ||
  ' where rownum < 2 having count(*) > 0;'
from 
  user_tables
order by table_name
;
spool off
spool empty_tables.log
@work.sql
spool off
exit

Regards
Jesper Haure Norrevang



--
//www.freelists.org/webpage/oracle-l

Other related posts:

  • » SV: Empty Table