Find Table Sizes in SQL Server: Rows, Data, Index and Unused Space

When disks start filling up, backups get longer or someone asks why a database has grown so much, the answer usually hides in a few large tables. Knowing how much space each table takes, and how much of that is data, how much is index and how much sits unused, is the starting point for archiving, compression and capacity decisions.

This article covers queries that calculate table sizes correctly, how to find the largest tables across all databases, and how to read the results.

A Query That Calculates Table Sizes

The most reliable source is the sys.dm_db_partition_stats view. It holds the reserved and used page counts for every partition; a page is 8 KB. The query below returns, for each table, the row count, the total reserved space and how that space splits into data, index and unused, in megabytes:

SELECT TOP (50)
       s.name + N'.' + t.name AS table_name,
       SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.row_count ELSE 0 END)  AS row_count,
       SUM(ps.reserved_page_count) * 8 / 1024.0                           AS reserved_mb,
       SUM(CASE WHEN ps.index_id IN (0, 1)
                THEN ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count
                ELSE 0 END) * 8 / 1024.0                                  AS data_mb,
       SUM(CASE WHEN ps.index_id > 1 THEN ps.used_page_count ELSE 0 END) * 8 / 1024.0 AS nonclustered_index_mb,
       SUM(ps.reserved_page_count - ps.used_page_count) * 8 / 1024.0      AS unused_mb
FROM sys.dm_db_partition_stats AS ps
JOIN sys.tables  AS t ON t.object_id = ps.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
GROUP BY s.name, t.name
ORDER BY reserved_mb DESC;

This query reads SQL Server’s metadata; it doesn’t scan the tables and is safe to run in production. index_id 0 is a heap, 1 is the clustered index (the table’s data itself), and values above 1 are nonclustered indexes.

How to Read the Results

• Large data_mb, small nonclustered_index_mb: the table itself is big. It’s a candidate for archiving, partitioning or compression.
• nonclustered_index_mb close to or above data_mb: the table has many or very wide indexes. Check for unused and duplicate indexes; we cover the details in our index guide.
• High unused_mb: space is reserved for the table but not used. It’s common in heaps after large deletes.
• Significant LOB pages: a high lob_used_page_count means large documents or files are stored in nvarchar(max), varbinary(max) or XML columns; keeping them outside the database may be worth considering.

For a Single Table: sp_spaceused

For a quick check, sp_spaceused is enough. Called with a table name, it returns the row count and the reserved, data, index and unused space; called without parameters, it shows the database as a whole:

EXEC sys.sp_spaceused N'dbo.Orders';
EXEC sys.sp_spaceused;   -- whole database

In SSMS, the Reports › Standard Reports › Disk Usage by Top Tables report (right-click the database) shows the same information graphically.

Largest Tables Across All Databases

To find the largest tables in every database on a server, run the query in each database and collect the results in one table:

IF OBJECT_ID(N'tempdb..#TableSizes') IS NOT NULL DROP TABLE #TableSizes;
CREATE TABLE #TableSizes (database_name sysname, table_name nvarchar(300),
                          row_count bigint, reserved_mb decimal(18, 2));

DECLARE @db sysname, @sql nvarchar(max);
DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE state_desc = N'ONLINE' AND database_id > 4;

OPEN dbs;
FETCH NEXT FROM dbs INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'USE ' + QUOTENAME(@db) + N';
        SELECT DB_NAME(), s.name + N''.'' + t.name,
               SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.row_count ELSE 0 END),
               SUM(ps.reserved_page_count) * 8 / 1024.0
        FROM sys.dm_db_partition_stats AS ps
        JOIN sys.tables  AS t ON t.object_id = ps.object_id
        JOIN sys.schemas AS s ON s.schema_id = t.schema_id
        GROUP BY s.name, t.name;';
    INSERT INTO #TableSizes EXEC sys.sp_executesql @sql;
    FETCH NEXT FROM dbs INTO @db;
END
CLOSE dbs;
DEALLOCATE dbs;

SELECT TOP (50) * FROM #TableSizes ORDER BY reserved_mb DESC;

Our cursor guide explains the differences between loop patterns and when a WHILE loop with a queue table is the more flexible choice.

What Would Compression Save?

On large tables, data compression can reduce both disk space and the number of pages read. Estimate the gain before applying it:

EXEC sys.sp_estimate_data_compression_savings
     @schema_name = N'dbo', @object_name = N'Orders',
     @index_id = NULL, @partition_number = NULL, @data_compression = N'PAGE';

Checklist

• The largest tables and their data / index / unused split are known.
• Tables whose index space approaches their data size have been checked for unused indexes.
• Heaps with large unused space have a clustered index or rebuild planned.
• Compression savings have been estimated for large tables.
• Sizes are recorded regularly and the growth trend is monitored.