CODEWITHSUNDEEP
oracle-database
aamir saif
aamir saif

Reputation: 13

query to find number of empty tables in my schema name HR

I want to find the number of empty tables in my schema hr and i have written a code for it:

set serveroutput on;


Declare
d Number;
c varchar2(25);
cursor c1 is SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER = 'HR';

Begin
 for r in c1
Loop
select count(*) into d
from (r.object_name);
if (d = 0) then
dbms_output.put_line(r.object_name||'is Empty');
end if;
end loop;
end;

but its throwing error at this line from (r.object_name). can anyone help me in this?

Upvotes: 1

Views: 457

Answers (3)

user330315
user330315

Reputation:

Querying dba_tables.num_rows is not reliable as that is an estimate. If the table has never been analyzed, it might not reflect the correct row count.

Dynamic SQL is also not required for this. Based on my answer that calculates the row count for all tables, you can simply add a where condition:

select table_name
from (
  select table_name, 
         to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C')) as row_count
   from all_tables
   where owner = 'HR'
)
where row_count = 0;

Upvotes: 3

shivam
shivam

Reputation: 491

In Oracle , you can use below query.

select    count(*) from    dba_tables  where    OWNER = 'XXXX' and num_rows =0;

Upvotes: 0

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

You need dynamic SQL (execute immediate) for that:

declare
  d number;
  v_sql varchar2(1000);

  cursor c1 is select object_name 
    from all_objects 
    where object_type = 'TABLE' and owner = 'HR';

begin
  for r in c1 loop
      v_sql := 'select count(*) from HR.'||r.object_name;
      execute immediate v_sql into d;
      if d = 0 then
          dbms_output.put_line(r.object_name||' is empty');
      end if;
  end loop;
end;

Upvotes: 1

Related Questions