Query to find all empty tables

Question

Considering that I have a Schema named SBST

I want to find all empty tables list in this SBST Schema. Is there any PL/SQL procedure to find that. I found few. But those were using user tables where I was not able specify the Schema name SBST.

I was using this

select table_name from dba_tables where owner ='SBST'
having count(*)=0  group by table_name

What's wrong in the above query?

Answer 1

Similar to @shareef's answer, but using dynamic SQL to avoid having to create the temporary .sql file. You'll need dbms_output to be visible, e.g. with set serveroutput on in SQL*Plus - don't know about Toad.

declare
    cursor c(p_schema varchar2) is
        select 'select ''' || table_name || ''' from ' ||
            p_schema ||'.' || table_name || ' where rownum < 2 ' ||
            ' having count(*) = 0' as query
        from all_tables
        where owner = p_schema
        order by table_name;
    l_table all_tables.table_name%TYPE;
begin
    for r in c('SBST') loop
        begin
            execute immediate r.query into l_table;
        exception
            when no_data_found then continue;
        end;

        dbms_output.put_line(l_table);
    end loop;
end;
/

Using all_tables seems more useful than dba_tables here so you know you can select from the tables it lists. I've also included the schema in the from clause in case there other users have tables with the same name, and so you can still see it if you're connected as a different user - possibly avoiding synonym issues too.

---

Specifically what's wrong with your query... you've got the having and group by clauses the wrong way around; but it will always return no data anyway because if SBST has any tables then count (*) from dba_tables must be non-zero, so the having always matches; and if it doesn't then, well, there's no data anyway so there's nothing for the having to match against. You're counting how many tables there are, not how many rows are in each table.

Answer 2

You can fire below query to find the list of tables haveing no data

select * from ALL_TABLES 
where owner ='SBST'
AND NUM_ROWS = 0;

Answer 3

the straight forward answer is

select 'select ''' || table_name || ''' from ' || table_name || ' 
    having count(*) = 0;' from dba_tables where owner='SBST';

EXPLANATION

You can run this... it will just output the table names of those having no rows: assuming you use sqlplus but i used toad to test it and it worked very well set echo off heading off feedback off lines 100 pages 0;

spool tmp.sql
select 'select ''' || table_name || ''' from ' || table_name || ' 
having count(*) = 0;' from user_tables where owner='SBST';
spool off;

@tmp.sql 

If you open the "tmp.sql" file, you'll see for all tables....

select 'PERSONS' from PERSONS having count(*) = 0;
select 'DEPARTMENT' from DEPARTMENT having count(*)=0; 

in your case you want a schema and schema is a user right the above code if you connect with the user SBST but if you connect with other then you have to use DBA_TABLES and assign owner attribute to SBST

USER_TABLES is tables which you own ALL_TABLES is tables which own, and tables owner by other users, which you have been granted excplicit access to DBA_TABLES is all tables in the database

like this

set echo off heading off feedback off lines 100 pages 0;

spool tmp.sql
select 'select ''' || table_name || ''' from ' || table_name || ' 
having count(*) = 0;' from dba_tables where owner='SBST';
spool off;

@tmp.sql 

All three are views of the underlying SYS tables, but the USER_ and ALL_ views joing in your username/security info to limit the results

**SUMMARY **

PLEASE JUST RUN THIS QUERY

select 'select ''' || table_name || ''' from ' || table_name || ' 
    having count(*) = 0;' from dba_tables where owner='SBST';