CODEWITHSUNDEEP
sqloracle-database
sqlgrasshopper5
sqlgrasshopper5

Reputation: 119

Selecting all tables with no records

I need to display all tables that have zero records.

I tried,

select * from user_all_tables where (select count(*) from user_all_tables)=0;

But it doesn't seem to work. How should I go about redesigning this query? Thanks.

Upvotes: 5

Views: 4310

Answers (5)

Stephanie Page
Stephanie Page

Reputation: 3893

This answer is one Fetch-per-table more efficient than Rene's. SELECT INTO requires an Extra Fetch to see if the "TOO_MANY_ROWS" exception should be raised. We can take control of that process with an explicit cursor and NOT doing an unnecessary extra fetch.

Declare
  cnt PLS_INTEGER;
  s_Cur Varchar2(255);
  c_Cur Sys_refcursor;
Begin
   For c In ( Select table_name From user_tables ) Loop

      s_Cur := 'Select 1  From dual where exists (select 1 from ' || c.table_name ||')';    

      Open c_Cur For s_cur ;
      Fetch c_cur into cnt;        
      If c_cur%NOTFOUND then 
          dbms_output.put_line( c.table_name );
      end if;

   End Loop;
End;

Upvotes: 0

Peter Lang
Peter Lang

Reputation: 55524

If all of your tables are analyzed, you can check the column num_rows of table user_tables.

Otherwise, you will need PL/SQL to make this work. This will output all tables of your current user without records (use all_tables if you need tables of other users):

Set Serveroutput On;

Declare
  cnt PLS_INTEGER;
Begin
  For c In ( Select table_name From user_tables ) Loop
    Execute Immediate 'Select Count(*) From "' || c.table_name || '" where rownum=1'
            Into cnt;
    If( cnt = 0 ) Then
      dbms_output.put_line( c.table_name );
    End If;
  End Loop;
End;

Upvotes: 6

Rene
Rene

Reputation: 10541

Variation of the accepted answer but using a more efficient method.

Declare
  cnt PLS_INTEGER;
Begin
  For c In ( Select table_name From user_tables ) Loop
    begin
       Execute Immediate 'Select 1 From dual where exists (select 1 from ' || c.table_name ||')' Into cnt;
    exception when no_data_found then
      dbms_output.put_line( c.table_name );
    end;  
  End Loop;
End;

Upvotes: 2

Rob van Wijk
Rob van Wijk

Reputation: 17705

You'd have to resort to PL/SQL and issue a select count(*) for every table. Or you can use dbms_xmlgen to do this for you in a tricky way:

select table_name
  from ( select table_name
              , extractvalue
                ( dbms_xmlgen.getxmltype('select count(*) c from '|| table_name)
                , '/ROWSET/ROW/C'
                ) cnt
              , rownum to_prevent_predicate_push
           from user_tables
       )
 where cnt = '0'

Regards, Rob.

Upvotes: 3

Cătălin Pitiș
Cătălin Pitiș

Reputation: 14341

select TABLE_NAME
from USER_ALL_TABLES
where NUM_ROWS = 0

Upvotes: 0

Related Questions