Free SQL Help for Devs, DBAs, and the Curious

Notes from the Field:

Today, while working with a large SQL Server database, I needed to understand how space was allocated across the many schemas, not just at the database or table level. Here's a breakdown of the query and methodology I used to calculate table sizes grouped by schema in SQL Server.

While this query works across multiple SQL Server versions, one of the standout features of SQL Server 2025 is its enhanced integration with Azure SQL, enabling seamless pipelines from the server to reports. In conjunction with that capability, optimizing schema level storage offers better control over report performance, export size, and overall database health.

Here is the query I used to calculate table sizes grouped by schema:

-- SQL
SELECT
    s.name AS SchemaName,
    ROUND(SUM(a.total_pages) * 8.0 / 1024 / 1024, 2) AS TotalSizeGB,
    ROUND(SUM(a.used_pages)  * 8.0 / 1024 / 1024, 2) AS UsedSizeGB,
    ROUND(SUM(a.data_pages)  * 8.0 / 1024 / 1024, 2) AS DataSizeGB
FROM sys.tables t
JOIN sys.schemas s
    ON t.schema_id = s.schema_id
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.is_ms_shipped = 0
GROUP BY s.name
ORDER BY TotalSizeGB DESC;

Output Columns Explained

TotalSizeGB
This is the total allocated space for the tables in a schema, including data, indexes, and any unused reserved space.

UsedSizeGB
This is the space actually used by the tables and their associated indexes. It reflects active storage usage.

DataSizeGB
This is the size of just the data pages, excluding any space used by indexes. It reflects the true size of the raw data itself.

This level of schema aware visibility helps identify storage heavy schemas, support archiving decisions, assist in data warehouse scaling, and guide partitioning strategies. It is a practical tool in any DBA toolkit, especially in environments with many schemas or complex ETL processes.

The Data Whisperer
SQLSupport.org

Check out the latest articles from all our sites:

• How to Take Advantage of Flash Sales at Grocery Stores [https://www.ethrift.net]
• A brief history of the Galveston Hurricane of 1900 [https://www.galvestonbeachy.com]
• How to Plant and Maintain Chokeberry Bushes [https://www.gardenhomes.org]
• New Query Store Enhancements in SQL Server 2022 [https://www.sqlsupport.org]
• Heat: Why My Laptop Is Cooking My Lap [https://www.SupportMyPC.com]
• The Best Months to Visit South Korea for Cherry Blossoms and Fall Colors [https://www.treasureholidays.com]