Script to find out Disk Space Per Table

Useful script to find out how much disk space each table takes up by querying the system tables.

Hope that helps, Jon Bosker, DB Gurus, 23 Nov 2015


SELECT 
T.name AS [TableName],
SUM(p.rows) AS [RowCount],
((SUM(a.data_pages) * 8) / 1024) AS SpaceUsedMB
FROM sys.tables T
JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
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 T.name NOT LIKE 'dt%' 
AND I.OBJECT_ID > 255 
AND I.index_id <= 1
GROUP BY T.NAME, I.object_id, I.index_id, I.name 
ORDER BY ((SUM(a.data_pages) * 8) / 1024) DESC