How to Find Empty Table in SQL Server

Today I have a good tip to share about find empty table in SQL Server. Sometime you created table in database, but you didn’t insert any rows. Once day you want to optimize or want to delete some table, you should run this query to know which empty table. It is necessary to clarifies the empty table for prevent lost your data before you delete it.

Please run this query to find the empty table in your database.

;WITH EmptyRows AS

(

   SELECT SUM(row_count) AS [TotalRows],

          OBJECT_NAME(OBJECT_ID) AS TableName

   FROM sys.dm_db_partition_stats

   WHERE index_id = 0 OR index_id = 1

   GROUP BY OBJECT_ID

)

SELECT * FROM EmptyRows

WHERE [TotalRows] = 0

Then please see the result:

All tables above don’t have data. But if have any transaction, it will have data don’t show when querying.