The fastest way to check table size in SQL Server is a single query against sys.dm_db_partition_stats and sys.allocation_units, joined back to sys.tables — it returns every table’s row count, reserved space, and used space in one pass, with no third-party tool required.
sp_spaceused answers the question for one table at a time, which is fine until you actually need to know which of your 400 tables is eating the disk. The queries below cover both cases, plus the point-and-click SSMS report for when you just want a quick look without writing SQL.
Table of contents
- sp_spaceused: the size of one table
- Every table at once, sorted by size
- The same reserved, data, and index breakdown for every table
- The SSMS Disk Usage by Top Tables report
- Reading reserved, data, index, and unused correctly
- Why this matters for backup time and storage cost
- What to do once you’ve found the big tables
- How this fits the rest of the stack
- FAQ
sp_spaceused: the size of one table
sp_spaceused is the built-in stored procedure for a single table’s footprint, and its output columns are exactly the four numbers people mean when they say “table size”: reserved, data, index_size, and unused.
EXEC sp_spaceused 'dbo.Orders';
-- Force a fresh count instead of the cached one
EXEC sp_spaceused 'dbo.Orders', @updateusage = 'TRUE';The @updateusage flag matters more than people expect. SQL Server caches these numbers, and after a large delete or a bulk load the cached figures can lag reality by a surprising margin. Run it with @updateusage = 'TRUE' before you trust the output on a table that changed recently, and skip the flag on a quick check where an approximate answer is fine — the update pass does a full scan of the table’s allocation units and is not free on a table with millions of rows.
Every table at once, sorted by size
The sp_spaceused procedure only takes one table name, so for a full inventory you go straight to the DMVs it reads internally. This is the version I keep pinned in a snippet manager, and it is read-only and safe to run against production.
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
FROM sys.tables t
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
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 TotalSpaceKB DESC;This walks sys.partitions for every index on every table and sums the pages behind it through sys.allocation_units, which is exactly what a table’s on-disk footprint actually is: a set of 8KB pages. Divide the KB columns by 1024 for MB, or wrap the whole thing in a CAST(... AS DECIMAL(18,2)) if you want cleaner formatting.
The same reserved, data, and index breakdown for every table
If you specifically want sp_spaceused’s exact column names — reserved, data, index_size, unused — across every table rather than the simplified total from the DMV join above, loop it into a temp table with the (undocumented but widely used) sp_MSforeachtable.
CREATE TABLE #TableSizes (
TableName NVARCHAR(128),
RowCounts VARCHAR(20),
Reserved VARCHAR(20),
Data VARCHAR(20),
IndexSize VARCHAR(20),
Unused VARCHAR(20)
);
EXEC sp_MSforeachtable 'INSERT INTO #TableSizes EXEC sp_spaceused ''?''';
SELECT *,
CAST(REPLACE(Reserved, ' KB', '') AS INT) AS ReservedKB_Sort
FROM #TableSizes
ORDER BY ReservedKB_Sort DESC;
DROP TABLE #TableSizes;This is slower on a database with thousands of tables because it runs one stored procedure call per table instead of one set-based query, and sp_MSforeachtable is undocumented and unavailable on Azure SQL Database. On Azure SQL, or if you want a fully supported alternative, stick with the DMV query above — it gives you the ranking you actually need (which tables are largest) even if the column names differ slightly.
The SSMS Disk Usage by Top Tables report
If you would rather not write SQL at all, SSMS ships a built-in report for exactly this. Right-click the database in Object Explorer, choose Reports, then Standard Reports, then “Disk Usage by Top Tables”.
- Shows every table ranked by total space, with data and index space broken out as separate bars
- Row count and reserved space are visible without running a query
- Export to PDF or Excel directly from the report toolbar, useful for a monthly capacity-review email
- Refreshes live against the connected database, so the numbers reflect the current state, not a cached snapshot
It is the same underlying data as the DMV query, presented as a chart. I reach for the report when I want a quick visual for a teammate and the query when I need the numbers in a script or a scheduled job.
Reading reserved, data, index, and unused correctly
Reserved is the total space SQL Server has allocated to the table, whether it is currently holding a row or not. Data is the portion of that space actually holding row data. Index_size is the space consumed by every nonclustered index (and the clustered index leaf level, which is the data itself, so a heap with no clustered index shows this differently than a table with one).
Unused is reserved minus data minus index_size — allocated pages that are not currently doing anything. A small unused figure is healthy. A large one, especially after a big delete, means the table has pages it is holding onto that it is not using, and a rebuild (ALTER INDEX ... REBUILD) will hand that space back to the filegroup instead of the table quietly hoarding it.
Why this matters for backup time and storage cost
Table size is a leading indicator for two costs that both scale with it directly: how long a full backup takes and how much you pay for storage. A table that doubled in size this quarter doubled its share of every backup window and every gigabyte-month you are billed for, and neither of those show up until the invoice or the missed maintenance window.
It is also the first place to look before a migration. If you are moving this workload off SQL Server, SQL Server to PostgreSQL Migration is worth reading before you commit to a cutover window, because the table you did not know was 40GB is the one that blows your maintenance-window estimate. A managed database that graphs storage growth on its own, the way the RunxBuild dashboard does for its Postgres and MySQL instances, turns this from a query you remember to run into a number you glance at.
What to do once you’ve found the big tables
Finding the largest tables is the easy part. Acting on it usually falls into one of three buckets: archive rows nobody queries anymore into a cheaper cold-storage table, add a filtered index instead of a full one if most queries touch a small recent slice, or partition the table by date if it is genuinely large and growing without bound.
Check database backup strategy before you archive anything — a table that is large because of years of audit history is often exactly the table someone will need restored one day, and “delete the old rows” and “back up the old rows somewhere cheaper first” are different plans with different risk.
How this fits the rest of the stack
RunxBuild does not run managed SQL Server today — the managed database options are MySQL and Postgres — so if this table is heading toward a migration rather than staying put, the storage and backup math you just ran is exactly what to model before you commit to a plan. The RunxBuild hosting calculator lets you put a database, a web service, and storage side by side and see what the combination actually costs before the migration weekend, not after.
Useful related references:
- Restoring a .bak File to SQL Server: SSMS and T-SQL, Step by Step
- SQL Server to PostgreSQL Migration: The Type Map, the T-SQL That Breaks, and a Cutover Plan That Does Not Lose Rows
- SQL Query to Get Data for the Last 1 Year: MySQL, PostgreSQL and SQL Server, Without Killing the Index
- Databases on RunxBuild
FAQ
What is the difference between reserved and data in sp_spaceused output?
Reserved is every page SQL Server has allocated to the table, including index pages and pages not currently holding data. Data is only the pages holding actual row data. The gap between them is index_size plus unused space — a table can have a large reserved figure and a much smaller data figure if it carries several nonclustered indexes or has unused space from deletes.
How do I check table size without running sp_spaceused on every table?
Use the DMV query against sys.tables, sys.indexes, sys.partitions, and sys.allocation_units. It is a single set-based query that returns every table’s size in one result set, which is faster than looping sp_spaceused when you have hundreds of tables and just want a ranked list.
Does checking table size lock the table?
No. Both sp_spaceused and the DMV queries read metadata and allocation counters rather than scanning table rows, so they do not take locks that block reads or writes. The @updateusage = ‘TRUE’ option does more work internally to refresh cached counts, but it still does not block normal query activity.
How often should I check table size?
A monthly check is enough for most applications to catch unexpected growth before it becomes a backup-window or storage-cost surprise. Check it immediately after any bulk load, bulk delete, or schema change that adds a new index, since those are the events that move the numbers the most.
Why is index_size bigger than data for some tables?
This happens on tables with several nonclustered indexes, especially wide ones covering many columns, or composite indexes with included columns. Each nonclustered index stores its own copy of the indexed columns, so a table with five indexes covering overlapping columns can easily have more total index storage than the base row data itself.