Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Finding Empty Tables in SQL Server DataBase

  • 16 April 2013
  • Author: Pradeep Dahiya
  • Number of views: 8703
  • 0 Comments

Here’s a simple query to find all empty tables in your SQL Server database that uses a VIEW called dm_db_partition_stats which returns page and row-count information for every partition in the current 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


OUTPUT
  
image

 

Print
Categories: Analysis Services
Tags:
Rate this article:
No rating

Please login or register to post comments.