Last Updated: February 25, 2016

SQL Server: Space Usage by Table

Sometimes an SQL Server database is much larger than you expect it to be. To figure out which tables are using all the data, run the following SQL against your database:

SELECT
 t.NAME AS TableName,
 s.Name AS SchemaName,
 p.rows AS RowCounts,
 SUM(a.total_pages) * 8 AS TotalSpaceKB,
 SUM(a.used_pages) * 8 AS UsedSpaceKB,
 (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
 sys.tables t
 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
 INNER JOIN sys.allocation_units a
 ON p.partition_id = a.container_id
 LEFT OUTER JOIN sys.schemas s
 ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%'
 AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 
GROUP BY t.Name, s.Name, p.Rows
ORDER BY TotalSpaceKB DESC

The result looks like this:

Picture