If you’re developing in SQL Server and storing data to tables, it’s important to frequently check the sizes of tables in your database. In this post, we explore various methods to check table sizes in SQL Server, catering to different scenarios and preferences. The order below to get table sizes in SQL Server below are in order of my preferred of methods.

Get Table Sizes in SQL Server

1. MSSQL Script to Get Table Sizes

As a SQL Database Administrator, my preferred method of obtaining the sizes of tables in SQL Server is by running a TSQL Script. The script below gives information on table names, schema, row counts, total space, used space, and unused space, providing a comprehensive overview of table utilization.

-- 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 t.Name;

2. Disk Usage by Top Tables Report

I like the Disk Usage by Top Tables Report which is an offering within SQL Server Management Studio (SSMS). It’s a quick way to review all tables in a database checking row counts and table sizes.

To open the Disk Usage by Top Tables Report, right click your database, select Reports, Standard Reports, and Disk Usage by Top Tables. Check out my other blog post for more information on the Disk Usage by Top Tables SSMS Report if useful.

3. MSSQL sp_spaceused System Stored Procedure

For another quick way to retreive the size of a tables in SQL Server, we can run the sp_spaceused SQL command. Add schema.tablename as parameters in the quotes for the table you’d like to check.

-- Scope to your database
USE Jupiter;
GO

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

4. Check Table Properties in SSMS

To check the size of a table via the SSMS Object Explorer, we can right click the table and select Properties, then navigate to the Storage tab. Here, you’ll find detailed information on index space, data space usage, and table row count, and some other metadata.