Monitoring table sizes in SQL Server is crucial for effective database management. For a detailed guide covering various methods, including built-in reports and custom scripts, please refer to my latest article on this: How to Check Table Sizes in SQL Server.
In this guide, we’ll show a few quick ways to show table sizes in MSSQL, including via GUI (SSMS) and sharing a SQL Script:
> Get Table Sizes in SSMS
> SQL Script to Get Table Sizes
SSMS Get Table Sizes in SSMS
For a quick overview of table sizes, we can use the Disk Usage by Top Tables Report in SQL Server Management Studio (SSMS).
To open this built-in SSMS Report:
1. Open SQL Server Management Studio (SSMS).
2. Expand Databases.
3. Right-click the Database and select Reports > Standard Reports > Disk Usage by Top Tables
The report shows that this database has 2 tables close to 700MB in size, both containing the same number of rows but there are some differences in the indexes.
If you’re investigating a specific table, opening the Table Properties within SSMS Object Explorer is the way to do with via GUI.
To open the Table Properties Window, first expand the database and table objects within SSMS and find the table you’re looking for. You can add a filter to help find your table, if needed.
Next, right-click the table and select Properties:
Open the Storage tab on the left-hand sidebar. It may take a second for the window to load, but this will show the index space, data space usage and the table row count:
Both of the above ways to get table info within SSMS are very useful, however they’re not practical ways for exporting data for comparison, or automation (monitoring).
SQL Script to Get Table Sizes
For detailed information across all tables, including schema info, row count, and space usage, run this query:
-- Get Table Size SQL Server Script
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB DESC
Why this method?
> Comprehensive results for all tables
> Easily exportable to CSV, Excel, or reports
> Ideal for database administrators (DBAs) needing insights for capacity planning
If you’re a DBA or developer working on capacity planning or performance tuning, this SQL script is the most flexible method. For quick checks we can use SSMS, as we have that open all day as DBA’s anyway!