Getting List of All Empty Tables in SQL Server

Hello everyone,

In this article I will try to give information about getting list of all empty tables in SQL Server.

In SQL Server you may want to get a list of all empty tables in some cases.

You can easily do this using the code below.

s.name schema_name,
       SUM(p.rows) total_rows
FROM sys.tables t
    JOIN sys.schemas s
        ON (t.schema_id = s.schema_id)
    JOIN sys.partitions p
        ON (t.object_id = p.object_id)
WHERE p.index_id IN ( 0, 1 )
GROUP BY t.name,
         s.name
HAVING SUM(p.rows) = 0;

When you run the above code, you will see the following result.

As you can see, all empty tables are listed.

Good luck to everyone in business and life.