Microsoft SQL Server – show total size allocated in MB per table

To get an overview of total disk space allocated per table in MB, use this SQL script:

WITH TableSizes AS
(
    SELECT 
        sch.name AS SchemaName,
        t.name AS TableName,
        SUM(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
    INNER JOIN 
        sys.schemas sch ON t.schema_id = sch.schema_id
    WHERE 
        t.type = 'U' -- Only include user tables
    GROUP BY 
        sch.name, t.name
)
SELECT 
    SchemaName,
    TableName,
    RowCounts,
    --TotalSpaceKB,
    --UsedSpaceKB,
    --UnusedSpaceKB,
    CAST(TotalSpaceKB / 1024.0 AS DECIMAL(10, 0)) AS TotalSpaceMB
    --CAST(UsedSpaceKB / 1024.0 AS DECIMAL(10, 2)) AS UsedSpaceMB,
    --CAST(UnusedSpaceKB / 1024.0 AS DECIMAL(10, 2)) AS UnusedSpaceMB
FROM 
    TableSizes
ORDER BY 
    TotalSpaceKB DESC;