SQL Server Get Table and Index Storage Size Report Script

calendar May 24, 2026 / · 7 min read · sql server storage index size table size monitoring sql scripts sys.dm_db_partition_stats sys.indexes sys.tables capacity planning database administration sys.dm_db_index_physical_stats sys.dm_db_index_usage_stats sys.objects sys.partitions  ·
Share on: linkedin copy

Knowing how much space each table and each index consumes is the foundation of capacity planning, archive policy, and index cleanup decisions on any SQL Server instance. This T-SQL script reads the partition-level storage statistics out of sys.dm_db_partition_stats and reports the size of every index — clustered, non-clustered, and the heap row that represents the table itself — in both kilobytes and megabytes, schema- and table-named for easy review.

Purpose and Overview

Backup windows that creep, restore tests that get skipped, archive policies that never get written — all three trace back to the same missing input: a current, per-object inventory of bytes-on-disk. sys.dm_db_partition_stats carries the page counts that answer the question; sp_spaceused only aggregates at the table or database level and loses the index-by-index detail this kind of capacity work needs. The query below converts those page counts to KB and MB for every index and every table in the current database, ready to paste into the next capacity-planning spreadsheet.

The script joins sys.dm_db_partition_stats to sys.indexes (to resolve the index name) and sys.tables (to resolve the table name and schema). It aggregates the used_page_count column across partitions for each (table, index) pair, multiplies by 8 to convert pages to kilobytes (every SQL Server page is exactly 8 KB), and reports both KB and MB in a single result set sorted by table.

Code Breakdown

The complete script is shown below, followed by an explanation of each section. Run it in the database whose storage you want to report on.

 1SELECT SCHEMA_NAME(t.schema_id)                    AS [Schema Name],
 2       t.[name]                                    AS [Table name],
 3       ix.[name]                                   AS [Index name],
 4       SUM(ps.[used_page_count]) * 8               AS [Index size (KB)],
 5       SUM(ps.[used_page_count]) * 8 / 1024.0      AS [Index size (MB)]
 6FROM   sys.dm_db_partition_stats AS ps
 7INNER JOIN sys.indexes AS ix
 8       ON ps.[object_id] = ix.[object_id]
 9       AND ps.[index_id] = ix.[index_id]
10INNER JOIN sys.tables t
11       ON t.OBJECT_ID = ix.object_id
12GROUP BY t.[name], ix.[name], t.schema_id
13ORDER BY t.[name];

sys.dm_db_partition_stats — Page Counts per Partition

sys.dm_db_partition_stats returns one row per partition per allocation unit and includes columns such as row_count, used_page_count, reserved_page_count, in_row_used_page_count, lob_used_page_count, and row_overflow_used_page_count. The script uses used_page_count — the count of pages that actually hold data plus the structural overhead — because it is the cleanest single measure of "how much space is this index actually taking." reserved_page_count is larger (it includes pages allocated but not yet used) and is appropriate when you care about pre-allocated growth rather than current footprint.

A page is fixed at 8 KB on every supported SQL Server edition, so multiplying the page count by 8 converts to KB. Dividing by 1024.0 (with the decimal to force decimal arithmetic) converts to MB.

Joining sys.indexes — Resolving the Index Name

Each sys.dm_db_partition_stats row identifies its index by the pair (object_id, index_id). Joining to sys.indexes on the same pair resolves the index name. Two subtleties to keep in mind:

  • The "table" itself (the heap row) appears with index_id = 0 and a NULL name in sys.indexes — that row represents the data rows of a heap table. If a table has a clustered index, the data rows live in the clustered index leaf level and there is no index_id = 0 row.
  • A clustered index has index_id = 1. Its size in this report is effectively the size of the table itself, because the clustered index leaf level is the table.

Joining sys.tables — Schema and Table Name Resolution

Joining sys.indexes.object_id to sys.tables.OBJECT_ID keeps only user tables (and excludes views and other object types that can also appear in sys.indexes). The SCHEMA_NAME(t.schema_id) call resolves the schema name from the schema_id column on sys.tables. The result set is grouped by table name, index name, and schema_id, which means each index of each table gets a single line in the report.

The Aggregation Pattern

The SUM(ps.[used_page_count]) aggregate handles partitioned tables transparently: a non-partitioned table has exactly one partition per index, so the SUM is the same as the single value; a partitioned table has multiple partitions, and the SUM totals them. Either way the output is "size of this index across all its partitions" — the right number for capacity planning.

Key Benefits and Use Cases

  • Per-index granularity: report the size of each non-clustered index separately from the table itself, exposing cases where the index footprint exceeds the data footprint
  • Capacity planning for backup duration, restore time, and disk forecasting at the object level
  • Identifies oversized tables and indexes quickly — paste the output into Excel and sort by MB descending to find the top space consumers
  • Partition-safe: the aggregation pattern handles partitioned tables transparently with no schema-specific code changes
  • Schema-named output for multi-schema databases such as Dynamics AX, NAV, or SharePoint, where unqualified table names collide
  • Pure DMV/catalog views, no DBCC: lightweight enough to run on a busy production server without measurable impact

Performance Considerations

  • Memory-resident metadata: sys.dm_db_partition_stats is a metadata DMV with no I/O against user pages; it is safe to run during peak hours
  • used_page_count is approximate between updates from internal background tasks. For exact byte-level accounting use DBCC SHOWFILESTATS or sys.dm_db_index_physical_stats with DETAILED mode — both heavier operations
  • Includes the heap row for tables without a clustered index: the heap appears with index_id = 0 and a NULL index name. Add WHERE ix.index_id > 0 if you want to suppress heap rows; add WHERE ix.[name] IS NOT NULL for the same effect
  • One row per partition is collapsed: SUM across partitions returns the total. To see per-partition sizes (for partition-aligned archive operations), drop the GROUP BY and select ps.partition_number directly
  • 8 KB page assumption: the multiply-by-8 is correct on every supported SQL Server edition. Don't change the constant — Azure SQL DB and Managed Instance use the same page size
  • Excludes system tables by joining to sys.tables rather than sys.objects — desired for application capacity work but means catalog views and DMVs don't appear in the report

Practical Tips

  • Schedule it as a SQL Server Agent job that writes results to a history table with a snapshot date. The day-over-day delta is the growth rate per object — pure gold for capacity planning
  • Sort by MB descending when investigating disk-full incidents to identify the largest single objects first
  • Compare clustered (index_id = 1) and non-clustered (index_id > 1) sizes per table to find tables where non-clustered indexes outweigh the data — a sign of over-indexing
  • Join to sys.dm_db_index_usage_stats to combine size with usage and prioritize cleanup of large unused indexes
  • For database-wide totals, wrap the SELECT in a subquery and SUM the MB column — fastest way to compute "this database is N GB" without sp_spaceused
  • For online compression analysis, pair with sys.partitions.data_compression to see which large indexes are uncompressed candidates for ROW or PAGE compression

Conclusion

This index size query is a reliable, lightweight T-SQL probe for per-index and per-table storage footprint. By reading sys.dm_db_partition_stats and translating page counts into KB and MB, it surfaces the per-object size data SQL Server tracks natively — partition-safe, schema-aware, and impact-free on a running production instance. Pair it with sys.dm_db_index_usage_stats for an index cleanup decision matrix, or schedule it nightly to build a growth-rate history that drives the next disk and backup-window forecast.

References

Posts in this series