How to Check Table Sizes in SQL Server

In this post, I’m sharing different ways of checking table sizes in SQL Server. There’s many ways to get this information, and whether you prefer using T-SQL scripts for automation, or SSMS tools for quick checks, these methods offer flexibility for different situations as a SQL Database Administrator.

If you’re working with SQL Server and storing data in tables, monitoring table sizes is important for performance tuning and disk space management. This is a topic all SQL Server users should be thinking about when performing certain changes while dealing with large tables. .

These methods I share will hopefully help you easily manage and track table sizes in SQL Server:
1. T-SQL Script to Get Table Sizes
2. Disk Usage by Top Tables Report
3. sp_spaceused System Stored Procedure
4. Table Properties in SSMS

Different Ways to Check Tables Sizes

1. T-SQL Script to Get Table Sizes

As a SQL Database Administrator, my preferred approach is using a T-SQL script.

This method provides a detailed view of table sizes, including table name, schema, row count, total space, used space, and unused space. The script is ideal for logging and monitoring table utilization over time.

-- Get Table Sizes in SQL Server  
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 t.Name;  

This script is particularly useful for identifying storage bottlenecks, unused space, and tables consuming significant resources.

2. Disk Usage by Top Tables Report

For a quick overview of table sizes, SSMS offers the Disk Usage by Top Tables report.

This built-in report provides a snapshot of table sizes, row counts, and index details for all tables in the database:

Just yesterday it was when I last used this report ^
I was asked to check table sizes on a Production environment, the developer did not have access and the db_datareader permissions request was awaiting approvals. He needed row counts and table size information for a few tables, and this was the most efficient way to provide him with that info.

3. sp_spaceused System Stored Procedure

Another quick and effective method is the sp_spaceused system stored procedure.
This built-in stored procedure returns size information for a specified table, including data space, index space, and reserved space.

-- Scope to your database  
USE Jupiter;  
GO  

-- Get table sizes using sp_spaceused  
EXEC sp_spaceused '[dbo].[MoonData]';  

This approach is best for checking specific tables without needing an entire database overview.

4. Table Properties in SSMS

If you’re investigating a specific table, opening the Table Properties within SSMS Object Explorer can be a useful and quick method.

To do this, expand the database and tables within the SSMS Object Explorer. You may need to add a filter to find your table if you have a lot of them.

Right-click a 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:

This method is handy for detailed table-specific information but isn’t practical for comparing multiple tables at once.