Size and Rows of Each Table in SQL Server, Indexes Included

To list the size and rows of each table, add up the pages in sys.dm_db_partition_stats. Split them into data, index and unused space. The result matches sp_spaceused, and it covers every table in one query.

Three Traps in the Common Query

The usual script joins sys.tables, sys.indexes, sys.partitions and sys.allocation_units. It keeps only index 0 and 1, the heap and the clustered index. Then it groups by table name. Jason Horner, a reader, found its integer rounding problem, and dividing by 1024.0 fixes it. The result looks right until the data gets real. The query has three traps, and each gets a fix below. It builds on Which Tables Are Huge: Rows Versus Size in SQL Server.

The demo database is named TableSizeTwoDemo. It holds an Orders table with two nonclustered indexes and a Notes table with large text. A table named Items exists in two schemas. The script loads the rows with GENERATE_SERIES, which needs SQL Server 2022 and compatibility level 160.

IF DB_ID(N'TableSizeTwoDemo') IS NULL CREATE DATABASE TableSizeTwoDemo;
GO
USE TableSizeTwoDemo;
GO
IF SCHEMA_ID(N'Archive') IS NULL EXEC (N'CREATE SCHEMA Archive');
DROP TABLE IF EXISTS dbo.Orders, dbo.Items, Archive.Items, dbo.Notes;
CREATE TABLE dbo.Orders (OrderID int IDENTITY(1,1) PRIMARY KEY, CustomerID int NOT NULL, Status char(10) NOT NULL, Note nvarchar(200) NULL);
CREATE INDEX IX_Orders_Customer ON dbo.Orders (CustomerID) INCLUDE (Status);
CREATE INDEX IX_Orders_Status ON dbo.Orders (Status);
CREATE TABLE dbo.Items (ItemID int PRIMARY KEY, ItemName nvarchar(60) NOT NULL);
CREATE TABLE Archive.Items (ItemID int PRIMARY KEY, ItemName nvarchar(60) NOT NULL);
CREATE TABLE dbo.Notes (NoteID int IDENTITY(1,1) PRIMARY KEY, Body varchar(max) NOT NULL);
INSERT INTO dbo.Orders (CustomerID, Status, Note)
SELECT value % 5000, CASE value % 3 WHEN 0 THEN 'Open' WHEN 1 THEN 'Shipped' ELSE 'Closed' END, REPLICATE(N'n', 50 + value % 100)
FROM GENERATE_SERIES(1, 50000);
INSERT INTO dbo.Items SELECT value, CONCAT(N'Item ', value) FROM GENERATE_SERIES(1, 300);
INSERT INTO Archive.Items SELECT value, CONCAT(N'Old item ', value) FROM GENERATE_SERIES(1, 1200);
INSERT INTO dbo.Notes (Body) SELECT REPLICATE(CAST('b' AS varchar(max)), 20000) FROM GENERATE_SERIES(1, 100);

The tables hold 50,000 orders, 300 current items, 1,200 archived items and 100 long notes. Here is the common query, with the decimal fix.

SELECT t.name AS TableName, SUM(p.rows) AS RowCounts, (SUM(a.total_pages) * 8) / 1024.0 AS TotalSpaceMB
FROM sys.tables AS t
INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
WHERE i.object_id > 255 AND i.index_id IN (0, 1)
GROUP BY t.name
ORDER BY TotalSpaceMB DESC;

Each row hides a problem. Orders reports 11.51 MB, but its two nonclustered indexes are missing. The filter keeps only the heap or clustered index. Notes reports 200 rows for 100 rows. A table with large text has two allocation units, and each one repeats the row count. Items shows 1,500 rows and one line. Two tables with the same name in different schemas were merged, because the query groups by name.

The Corrected Query

The view sys.dm_db_partition_stats holds the page counts for every partition, with one row for each index. It has no allocation unit duplicates, so the row count is not repeated. SQL Server documents row_count as an approximate value, and here it matched the rows loaded. The query below groups by schema and table. It counts rows only for the heap or clustered index. Data is the in-row, large object and overflow pages of that structure. Index space is everything used beyond data, and unused is reserved minus used.

SELECT s.name AS SchemaName, t.name AS TableName,
       SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.row_count ELSE 0 END) AS RowCounts,
       CAST(SUM(ps.reserved_page_count) * 8 / 1024.0 AS decimal(12,2)) AS ReservedMB,
       CAST(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 decimal(12,2)) AS DataMB,
       CAST((SUM(ps.used_page_count) - 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 decimal(12,2)) AS IndexMB,
       CAST((SUM(ps.reserved_page_count) - SUM(ps.used_page_count)) * 8 / 1024.0 AS decimal(12,2)) AS UnusedMB
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.dm_db_partition_stats AS ps ON ps.object_id = t.object_id
WHERE t.is_ms_shipped = 0
GROUP BY s.name, t.name
ORDER BY ReservedMB DESC;

Orders now reports 13.96 MB, and 2.22 MB of that are its indexes. Notes shows its 100 rows once. Both Items tables appear on their own lines with their own row counts.

Check It Against sp_spaceused

The system procedure sp_spaceused is the reference. It reports the same four figures for one table, in kilobytes. Run it for Orders and compare.

EXEC sp_spaceused N'dbo.Orders';

The reserved figure of 14,296 KB is 13.96 MB. Data is 11.29 MB, index size is 2.22 MB and unused is 0.45 MB. All four agree with the query. The query only does for every table at once what the procedure does for one.

Reading the Columns

Reserved is the space SQL Server set aside for the table and its indexes. Used is what holds real pages, and reserved minus used is unused. Unused space is normal. A table grows in blocks of eight pages, so some of the last block is empty. A large unused figure on a big table is worth a look after a heavy delete.

Index space tells you what the indexes cost. In the demo, the two nonclustered indexes of Orders take 2.22 MB next to 11.29 MB of data. Put that figure next to the number of queries each index helps. An index that costs space and helps nothing is a candidate for removal.

Limits of the Query

The query reads the page counts SQL Server keeps for each partition, so it never scans a table. That makes the size and rows of each table cheap to read, even in a big database. It needs permission to view database state. It covers tables only, so indexed views and internal tables are left out. The sizes include every partition of a partitioned table, added together.

You could argue that a script is more work than the disk usage reports in Management Studio. A report is fine for one look. A script runs in a job, writes to a table, and lets you track growth by day.

What to Remember

Report the size and rows of each table with sys.dm_db_partition_stats, not with a join to the allocation units. Group by schema and table. Count rows only for the heap or clustered index, and keep the indexes in the total. Check one table against sp_spaceused before you trust a new script.

When you finish, drop the demo database.

USE master;
GO
ALTER DATABASE TableSizeTwoDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TableSizeTwoDemo;

A table size is not one number, it is data, indexes and room to grow.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.