Query to find all empty tables

oracle, oracle11g

Solution

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.

Problem

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?

Original source