How to find tables with 0 records using space

This is a follow up post to the previous one about tables with no records using data space, with the idea coming from twitter of all places. Sometimes on twitter the conversations kill, but after reading that post my friend Chris Wood suggested writing something to FIND those tables.

Which is a solid idea, so let’s get to it. No time to wait too long.

Time to take a ride

First off, a hat tip to Pinal Dave for saving me too much trippin’ by writing a script that I could use as a starting point. I’ve adapted it for use here, specifically looking for any heap with 0 records that consume more than 1 megabyte of precious space.

The script checks all user databases on an instance, but if you want your investigation to go faster you can run it against your favorite Central Management Server.

SET NOCOUNT ON;

DECLARE @BigEmpty TABLE (
DatabaseName SYSNAME
, SchemaName VARCHAR(128)
, TableName VARCHAR(128)
, [RowCount] TINYINT
, TotalSpaceMB NUMERIC(9,2)
, UsedSpaceMB NUMERIC(9,2)
, DataSpaceMB NUMERIC(9,2)
);

DECLARE @SQL NVARCHAR(1000);

SELECT @SQL = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ?
SELECT
DB_NAME() AS DatabaseName
 , s.[Name] AS SchemaName
, t.[Name] AS TableName
, MAX(p.rows) AS [RowCount]
, CONVERT(NUMERIC(9,2),(SUM(a.total_pages)/128.0)) as TotalSpaceMB
, CONVERT(NUMERIC(9,2),(SUM(a.used_pages)/128.0)) as UsedSpaceMB
, CONVERT(NUMERIC(9,2), (SUM(a.data_pages)/128.0)) as DataSpaceMB
FROM sys.tables t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i
ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p
ON i.object_id = p.OBJECT_ID
AND i.index_id = p.index_id
JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE i.index_id = 0 /* heaps only */
GROUP BY
s.[Name]
, t.[Name]
HAVING MAX(p.rows) = 0
AND SUM(a.total_pages)/128.0 > 1.00; /*larger than 1 MB */
 
END' ;

INSERT @BigEmpty (
DatabaseName
, SchemaName
, TableName
, [RowCount]
, TotalSpaceMB
, UsedSpaceMB
 , DataSpaceMB
 )
EXEC master.sys.sp_MSforeachdb @SQL;

SELECT *
FROM @BigEmpty
ORDER BY 1,2,3;

Time to take her home

What you do with the results is up to you. As noted in my previous post you can reclaim the space in a few different ways, but you might also want to have a conversation with your database development folks about whether or not there should be a clustered index on any space-eating heaps you find. Ounce of prevention, pound of cure, and all that.

Hope this helps!