Get size of all tables in database

How to Get the Size of All Tables in a SQL Server Database

Learn various T-SQL methods to accurately determine the storage size of all tables within your SQL Server database, including data and index space.

Understanding the storage footprint of your database tables is crucial for performance tuning, capacity planning, and identifying potential data growth issues. SQL Server provides several ways to retrieve this information, ranging from simple system stored procedures to more detailed queries against system views. This article will guide you through the most common and effective methods to get the size of all tables in your SQL Server database.

Using sp_spaceused Stored Procedure

The sp_spaceused system stored procedure is a quick and easy way to get storage information for a specific table or the entire database. When executed without parameters, it provides a summary for the current database. When executed with a table name, it provides detailed space usage for that table, including data, index, and unused space.

EXEC sp_spaceused;

Get space usage for the entire database

EXEC sp_spaceused N'YourTableName';

Get space usage for a specific table

While sp_spaceused is convenient, it doesn't directly provide a list of all tables with their sizes in a single, easily queryable result set. To get this for all tables, you would typically need to iterate through them or use a more advanced query.

Querying System Views for Detailed Table Sizes

For a more comprehensive and customizable report on table sizes, querying system views like sys.tables, sys.partitions, and sys.allocation_units is the preferred method. This approach allows you to aggregate data and index space for each table, providing a clear picture of their storage consumption. This method is generally more accurate and flexible than sp_spaceused for a full database overview.

erDiagram
    "sys.tables" ||--o{ "sys.partitions" : "has"
    "sys.partitions" ||--o{ "sys.allocation_units" : "allocates"
    "sys.allocation_units" ||--o{ "sys.schemas" : "owned by"
    "sys.tables" { 
        int object_id PK
        nvarchar name
        int schema_id FK
    }
    "sys.partitions" { 
        int object_id PK,FK
        int partition_id PK
        bigint rows
        int index_id
    }
    "sys.allocation_units" { 
        int container_id PK
        int type
        bigint total_pages
        bigint used_pages
        bigint data_pages
    }
    "sys.schemas" { 
        int schema_id PK
        nvarchar name
    }

Simplified ER Diagram of relevant SQL Server system views for space usage

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    SUM(CASE WHEN i.index_id < 2 THEN a.data_pages ELSE 0 END) * 8 AS DataSpaceKB,
    SUM(CASE WHEN i.index_id > 1 THEN a.used_pages ELSE 0 END) * 8 AS IndexSpaceKB
FROM
    sys.tables t
INNER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
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
WHERE 
    t.is_ms_shipped = 0
GROUP BY 
    t.Name, s.Name, p.rows
ORDER BY 
    TotalSpaceKB DESC;

T-SQL query to get size of all user tables

Understanding the Output

The query above provides several key metrics for each table:

• TableName: The name of the table.
• SchemaName: The schema to which the table belongs.
• RowCounts: The approximate number of rows in the table. This count can sometimes be slightly out of date due to deferred updates.
• TotalSpaceKB: The total space allocated to the table, including data and indexes.
• UsedSpaceKB: The total space currently used by the table's data and indexes.
• UnusedSpaceKB: The difference between allocated and used space, indicating free space within the allocated extents.
• DataSpaceKB: The space consumed by the actual data rows.
• IndexSpaceKB: The space consumed by all indexes on the table.