how to find number of empty tables in any schema?

Posted on

Question :

I want to query how to find all the empty tables in my schema HR
i already tried with
select *
from dba_tables
where owner=’HR’ and num-rows is null;
but i dont know if this is the correct logic or not

Answer :

When you say ’empty’ tables, I take that to mean tables with zero rows.

Using the example in this post as a starting point,

WITH TablesAndRowCounts
AS (
    SELECT SCHEMA_NAME(sc.schema_id) SchemaName
        ,sc.NAME + '.' + ta.NAME TableName
        ,SUM(pa.rows) RowCnt
    FROM sys.tables ta
    INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
    INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id
    WHERE ta.is_ms_shipped = 0
        AND pa.index_id IN (1,0)
    GROUP BY sc.schema_id
FROM TablesAndRowCounts
WHERE rowcnt = 0
and SchemaName = 'HR'

Leave a Reply

Your email address will not be published. Required fields are marked *