How to Check Table Sizes in SQL Server

In this post, I’m sharing how to check 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 care about in the end.

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

For a more manual method, you can view table sizes directly in the Object Explorer of SSMS:
> Navigate to the Storage tab.
> Right-click on the table.
> Select Properties.

Right-click a table and select Properties.

Within the Storage tab we’ll see more of the same disk usage info as above, including overall table disk space, index space and row counts.