# SQL Server Get Size of All Tables in a Database

> Find the size of all tables in a SQL Server database using SSMS reports, sp_spaceused with sp_MSForEachTable, or sys.tables queries with allocation units

**URL:** https://sentry.io/answers/how-to-get-the-size-of-all-tables-in-a-sql-server-database/

---

## 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](https://github.com/lerocha/chinook-database/tree/master/ChinookDatabase/DataSources), 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 (SSMS), which is a free download from Microsoft.

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

### Option 2 — Use Stored Procedures

Run the query:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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      |

---

*Source: [sentry.io/answers/how-to-get-the-size-of-all-tables-in-a-sql-server-database/](https://sentry.io/answers/how-to-get-the-size-of-all-tables-in-a-sql-server-database/)*