List all empty tables in your SQL Server database

Here’s a simple query to find all empty tables in your SQL Server database.  We are using the dm_db_partition_stats DMV, which returns page and row-count information for every partition in the current database.  

   /*   Use the dm_db_partition_stats DMV to list all empty tables in your database.   */
   ;WITH Empty AS     
   (
    SELECT 
OBJECT_NAME(OBJECT_ID) [Table],
SUM(row_count) [Records]
    FROM 
sys.dm_db_partition_stats      
    WHERE 
index_id = 0 OR index_id = 1      
    GROUP BY 
OBJECT_ID      
   )      

   -- pull it back out
   SELECT [Table],Records 
   FROM [Empty]      
   WHERE [Records] = 0

   -- your results

   Table           Records

   sysclones          0

   sysseobjvalues     0

   syspriorities      0

   sysdbfrag       0

   sysfgfrag       0

   .........

   ......

   ..

This will provide you a little more information on the sys.dm_db_partition_stats DMV:

   http://msdn.microsoft.com/en-us/library/ms187737.aspx

Please remember, this is not guaranteed to be an accurate record count.  These counts are based on what is cached in memory, or stored on disk in varied system tables. Something I often refer people to is this post from Kalen Delaney. She shows different methods to get the row counts, and makes us ask ourselves what 'accurate' really means:

    http://sqlblog.com/blogs/kalen_delaney/archive/2009/12/07/how-many-rows.aspx

Wait. What about the tables that aren't used anymore, but are not empty?  We often have unused tables in the database that still contain data. In my book, if they're not used, get rid of them. Thus, preserving server resources for what is required, rather than old/unused objects that are no longer needed.

    http://www.sqlfingers.com/2014/02/find-unused-tables-in-sql-server.html