Finding Empty Tables in SQL Server

SQL Server

This query will return the names of all the empty tables in your database.

;WITH EmptyRows AS  
(  
   SELECT SUM(row_count) AS [CountRows],  
                   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

This can be helpful in locating any tables you have created, but never used.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s