Sunday, October 5, 2014

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     
SUM(row_count) [Records]
index_id = 0 OR index_id = 1      

   -- 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:

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:

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.

No comments:

Post a Comment