How to get the size of all tables in a SQL Server database

Richard C.

—November 15, 2023

The Problem

How do you find the size in MB of all your SQL Server tables? And how do you find the size of every table, in every database, on your server?

This is important if you suspect that your tables are taking up too much space, or growing too quickly. But SQL is an abstract language that returns only data inside a database, not information about the server itself.

The Solution

Finding the physical size of tables in a database depends on the server you are using. In SQL Server there are a few different ways.

In this article, we use the Chinook test data, available for every database server.

Option 1 — Use SQL Server Management Studio

The easiest way works only if you are using SQL Server Management Studio, which is not freely available.

Right-click on your database.
Select: Reports → Standard Reports → Disk Usage By Table.

Option 2 — Use Stored Procedures

Run the query:

Click to Copy

exec sp_MSForEachTable 'exec sp_spaceused [?]';

This is fast, free, and easy to remember for future use. But the output is cumbersome to extract into a spreadsheet to calculate totals.

Let’s discuss how it works. SQL Server provides the system-stored procedure sp_spaceused that shows the reserved, used, and unused space of a specified table.

You can use it through the terminal, or any IDE that can run queries against SQL Server. For example, here’s how to get the size of the album table in Chinook using sqlcmd in the terminal:

Click to Copy

use Chinook;
GO
exec sp_spaceused 'album';
GO

-- name          rows                 reserved           data               index_size         unused
-- Album         347                  144 KB             24 KB              32 KB              88 KB

Another stored procedure, sp_MSForEachTable, runs whatever procedure you pass to it against every table in the database. So if you pass it sp_spaceused, you can see the size of every table:

Click to Copy

exec sp_MSForEachTable 'exec sp_spaceused [?]';
GO


-- name         rows                 reserved           data               index_size         unused

-- Album        347                  144 KB             24 KB              32 KB              88 KB
-- name         rows                 reserved           data               index_size         unused

-- Artist       275                  72 KB              16 KB              16 KB              40 KB
-- name         rows                 reserved           data               index_size         unused

-- Customer     59                   144 KB             16 KB              32 KB              96 KB
-- And more...

Each table is returned as a separate result set making it difficult to work with the output. Use this option if you are interested in seeing individual tables.

Option 3 — Use `sys` Tables

The final option is the most comprehensive and configurable, but also the most complicated. The query below selects all tables from the sys objects that aren’t system tables:

Click to Copy

SELECT
    s.Name AS SchemaName,
    t.Name AS TableName,
    p.rows AS NumRows,
    CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB,
    CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
    CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB
FROM
    sys.tables t
    JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
    JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    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
    Total_MB DESC, t.Name

The result:

SchemaName	TableName	NumRows	Total_MB	Used_MB	Unused_MB
dbo	PlaylistTrack	8715	0.90	0.77	0.13
dbo	Track	3503	0.78	0.67	0.11
dbo	InvoiceLine	2240	0.27	0.20	0.08
dbo	Album	347	0.14	0.05	0.09
dbo	Customer	59	0.14	0.05	0.09
dbo	Employee	8	0.14	0.03	0.11
dbo	Invoice	412	0.14	0.09	0.05
dbo	Artist	275	0.07	0.03	0.04
dbo	Genre	25	0.07	0.02	0.05
dbo	MediaType	5	0.07	0.02	0.05
dbo	Playlist	18	0.07	0.02	0.05

If you want to see your tables in alphabetical order, remove the Total_MB DESC parameter in the ORDER BY clause.

Show All Tables For Every Database

Finally, you might want to see the table size of every table in every database on your server. To do this, call the query above from the stored procedure sp_MSforeachdb, storing the output for each database in the same temporary table:

Click to Copy

SET NOCOUNT ON;

USE [master];
GO

IF object_id('tempdb..#TableSizes') IS NOT NULL
    DROP TABLE #TableSizes;

CREATE TABLE #TableSizes
(
    recid          int IDENTITY (1, 1),
    DatabaseName   sysname,
    SchemaName     varchar(128),
    TableName      varchar(128),
    NumRows        bigint,
    Total_MB       decimal(15,2),
    Used_MB        decimal(15,2),
    Unused_MB      decimal(15,2)
)

EXEC sp_MSforeachdb 'USE [?];
INSERT INTO #TableSizes (DatabaseName, SchemaName, TableName, NumRows, Total_MB, Used_MB, Unused_MB)
SELECT
    ''?'' as DatabaseName,
    s.Name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS NumRows,
    CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB,
    CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
    CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB
FROM
    sys.tables t
    JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
    JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    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
    Total_MB DESC, t.Name';

SELECT   DatabaseName, SchemaName, TableName, NumRows, Total_MB, Used_MB, Unused_MB
FROM     #TableSizes
ORDER BY DatabaseName, SchemaName, TableName;

The result is the same if you have only one database on your server:

DatabaseName	SchemaName	TableName	NumRows	Total_MB	Used_MB	Unused_MB
Chinook	dbo	Album	347	0.14	0.05	0.09
Chinook	dbo	Artist	275	0.07	0.03	0.04
Chinook	dbo	Customer	59	0.14	0.05	0.09
Chinook	dbo	Employee	8	0.14	0.03	0.11
Chinook	dbo	Genre	25	0.07	0.02	0.05
Chinook	dbo	Invoice	412	0.14	0.09	0.05
Chinook	dbo	InvoiceLine	2240	0.27	0.20	0.08
Chinook	dbo	MediaType	5	0.07	0.02	0.05
Chinook	dbo	Playlist	18	0.07	0.02	0.05
Chinook	dbo	PlaylistTrack	8715	0.90	0.77	0.13
Chinook	dbo	Track	3503	0.78	0.67	0.11

Syntax.fmListen to the Syntax Podcast
Community SeriesIdentify, Trace, and Fix Endpoint Regression Issues
Listen to the Syntax Podcast
Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.
SEE EPISODES

Considered “not bad” by 4 million developers and more than 100,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

learn more about sentry join our discord

Platform

Languages & Frameworks

Why Sentry?

Features

How to get the size of all tables in a SQL Server database

The Problem

The Solution

Option 1 — Use SQL Server Management Studio

Option 2 — Use Stored Procedures

Option 3 — Use `sys` Tables

Show All Tables For Every Database

How to get the size of all tables in a SQL Server database

The Solution

Option 1 — Use SQL Server Management Studio

Option 2 — Use Stored Procedures

Option 3 — Use sys Tables

Show All Tables For Every Database

A peek at your privacy

Who we collect PII from

PII we may collect about you

How we use your PII

Third parties who receive your PII

We use cookies (but not for advertising)

Know your rights

Option 3 — Use `sys` Tables