How to Calculate Table Size by Schema in SQL Server

By Thomas Nonmacher

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’s 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.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's optimization kit, especially in environments with many schemas or complex ETL processes. 

The Data Whisperer
SQLSupport.org 

Check out the latest articles from all our sites:




D49069
Please enter the code from the image above in the box below.