Sentry Answers>SQL Server>

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

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

Richard C.

The ProblemJump To Solution

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:

SchemaNameTableNameNumRowsTotal_MBUsed_MBUnused_MB
dboPlaylistTrack87150.900.770.13
dboTrack35030.780.670.11
dboInvoiceLine22400.270.200.08
dboAlbum3470.140.050.09
dboCustomer590.140.050.09
dboEmployee80.140.030.11
dboInvoice4120.140.090.05
dboArtist2750.070.030.04
dboGenre250.070.020.05
dboMediaType50.070.020.05
dboPlaylist180.070.020.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, TableName, SchemaName, 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, TableName, SchemaName, 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:

DatabaseNameTableNameSchemaNameNumRowsTotal_MBUsed_MBUnused_MB
ChinookdboAlbum3470.140.050.09
ChinookdboArtist2750.070.030.04
ChinookdboCustomer590.140.050.09
ChinookdboEmployee80.140.030.11
ChinookdboGenre250.070.020.05
ChinookdboInvoice4120.140.090.05
ChinookdboInvoiceLine22400.270.200.08
ChinookdboMediaType50.070.020.05
ChinookdboPlaylist180.070.020.05
ChinookdboPlaylistTrack87150.900.770.13
ChinookdboTrack35030.780.670.11
  • Community SeriesIdentify, Trace, and Fix Endpoint Regression Issues
  • Syntax.fm logo
    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

Loved by over 4 million developers and more than 90,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.

© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.