How to Check Table Sizes in SQL Server

sp_spaceused returns rows, reserved, data, index_size, and unused for a single table, or for the whole database when called with no argument. The row count comes from cached metadata and can drift; pass @updateusage = 'true' to force a recount, which is slower but accurate.

EXEC sp_spaceused 'orders';

All tables, largest first

Aggregating sys.dm_db_partition_stats covers every table in one pass, reading cached metadata only, so it takes no locks and runs instantly even on a large database. Filtering index_id to 0 and 1 counts heap and clustered index rows only, which avoids multiplying the row count by the number of nonclustered indexes. Pages are 8 KB, hence the * 8 / 1024 to reach megabytes.

SELECT s.name AS schema_name,
       t.name AS table_name,
       SUM(CASE WHEN p.index_id IN (0, 1) THEN p.row_count ELSE 0 END) AS total_rows,
       SUM(p.reserved_page_count) * 8 / 1024 AS reserved_mb
FROM sys.dm_db_partition_stats p
JOIN sys.tables t ON t.object_id = p.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
GROUP BY s.name, t.name
ORDER BY reserved_mb DESC;