Re: Find Empty Tables

Whoops - too keen with the send button.

>
> You could try SELECT 'X' FROM DUAL WHERE EXISTS( SELECT 'X' FROM table).
>
> Oracle optimises EXISTS such that it only finds the first row before
ending
> that query (the subquery in the case above). If the select above (whole
> statement) returns 'X' (you could 1 or NULL or whatever it's trivial and
> irrelevant in the grand scale of things) then you have a non empty table.
>
> Andy
>

Clearly you'd want to script the creation of the above statement for every table rather than manually having to type it. Either using SQL*Plus and the spool option to generate a temporary script or using PL/SQL and dynamic SQL (or Pro*C or whatever). For those who might be interested and not familar with SQL*Plus this produces a list of tables with no rows for the current user. Create it as a script and run it through SQL*Plus (as a script the final output is better beautified)

set pagesize 0
set linesize 132
set trimspool on
set feedback off
set echo off
set termout off

spool tempscript.sql
select 'select '''||table_name||''' as "tables with no rows" from dual where not exists( select ''x'' from '||table_name||') ;' from user_tables;
spool off

set termout on
spool results
@tempscript.sql
spool off

Additionally Daniels idea is the most efficient if there are stats. You'd only need this one if you don't have up to date statistics.

Andy Received on Fri May 30 2003 - 04:57:13 CDT