;WITH
space_cte AS
(
SELECT t.NAME "TableName"
, p.rows "RowCounts"
, SUM(a.total_pages) * 8 "TotalSpaceKB"
, SUM(a.used_pages) * 8 "UsedSpaceKB"
, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 "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
WHERE t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY t.Name
, p.Rows
)
SELECT A.TableName
, A.RowCounts
, A.TotalSpaceKB
, ( case when A.UsedSpaceKB > 1000000 then cast(A.UsedSpaceKB / 1000000 As varchar(30)) + ' GB'
when A.UsedSpaceKB > 1000 then cast(A.UsedSpaceKB / 1000 As varchar(30)) + ' MB'
else cast(A.UsedSpaceKB as varchar(30)) + ' KB' end ) "UsedSpace"
, A.UnusedSpaceKB
FROM space_cte A
ORDER BY A.UsedSpaceKB desc
