Here’s a simple query to list all empty tables in your SQL Server database that uses a Dynamic Management 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
WHERE index_id = 0 OR index_id = 1
GROUP BY OBJECT_ID
SELECT * FROM EmptyRows
WHERE [TotalRows] = 0
Note that the results from the view is only approximate. If there are any active transactions that are inserting or deleting rows, the count may not include it.
Did you like this post?
|subscribe via rss||subscribe via e-mail|
|print this post||follow me on twitter|