Get Table Sizes

How to Get Table Sizes in SQL Server

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
	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
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255 
GROUP BY t.Name, s.Name, p.Rows
ORDER BY t.Name;
SQL Script Get Table Sizes

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.

SQL Disk Usage of Tables Report

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;

-- Get table sizes using system proc sp_spaceused
sp_spaceused '[dbo].[MoonData]';
sp_spaceused SQL Server Table Size

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.

MS SQL Table Properties

Leave a Reply

Your email address will not be published. Required fields are marked *