sysindexes vs sys.indexes: Legacy and Modern Catalog Views

calendar Jun 15, 2026 / · 6 min read · sql server sysindexes sys.indexes sys.partitions sys.allocation_units sys.dm_db_partition_stats sys.index_columns sys.objects sys.schemas catalog views compatibility views metadata database structure scripts  ·
Share on: linkedin copy

Plenty of SQL Server scripts still in circulation read rowcnt and dpages straight out of sysindexes — a habit inherited from SQL Server 2000, where that table was the canonical place to find row counts and page totals. The object survives only as the sys.sysindexes compatibility view, is flagged for removal, and returns inaccurate numbers for partitioned tables. This post shows the modern replacement and how to migrate.

Purpose and Overview

In SQL Server 2000, sysindexes was a real system table with one row per index and heap, and DBAs leaned on its rowcnt, dpages, reserved, and used columns for quick size and row-count reports. SQL Server 2005 rebuilt the metadata layer, and sysindexes was retained only as sys.sysindexes, a backward-compatibility view. Microsoft's documentation is explicit: this feature is included for backward compatibility, will be removed in a future version, and should not be used in new development.

The reason is more than housekeeping. sys.sysindexes does not support XML indexes, does not fully support partitioned tables and indexes, and its dpages, reserved, and used columns return wrong results when row-overflow data is present. A script that still reads those columns can silently report incorrect sizes on any modern database that uses partitioning or large-value columns.

The supported replacement is a small set of catalog views and one dynamic management view: sys.indexes for index definitions, sys.partitions for row counts, sys.allocation_units for page totals, and sys.dm_db_partition_stats as a convenient one-stop alternative. This script reproduces the classic row-count-and-space report using those views, and the same object_id-based metadata layer powers the ANSI-standard data-dictionary queries covered separately.

Code Breakdown

First, the legacy pattern you are migrating away from — shown only so you can recognize it in old scripts:

1-- Legacy SQL Server 2000-era pattern; avoid in new code.
2SELECT OBJECT_NAME(id) AS table_name, rowcnt, dpages
3FROM sys.sysindexes
4WHERE indid IN (0, 1);

The supported equivalent, which aggregates rows and space correctly across partitions:

 1SELECT
 2    s.name                                AS schema_name,
 3    t.name                                AS table_name,
 4    i.name                                AS index_name,
 5    i.index_id,
 6    i.type_desc,
 7    SUM(p.rows)                           AS row_count,
 8    SUM(au.total_pages) * 8 / 1024.0      AS total_space_mb,
 9    SUM(au.used_pages)  * 8 / 1024.0      AS used_space_mb
10FROM sys.indexes AS i
11INNER JOIN sys.objects AS t
12    ON i.object_id = t.object_id
13INNER JOIN sys.schemas AS s
14    ON t.schema_id = s.schema_id
15INNER JOIN sys.partitions AS p
16    ON  i.object_id = p.object_id
17    AND i.index_id  = p.index_id
18INNER JOIN sys.allocation_units AS au
19    ON p.partition_id = au.container_id
20WHERE t.is_ms_shipped = 0
21    AND t.type = 'U'
22GROUP BY s.name, t.name, i.name, i.index_id, i.type_desc
23ORDER BY total_space_mb DESC;

Mapping the old columns to the new views

The documented mapping is one-to-many: the single sysindexes table was decomposed into sys.indexes, sys.partitions, sys.allocation_units, and sys.dm_db_partition_stats. Index definitions (name, index_id, type) come from sys.indexes; the old rowcnt becomes sys.partitions.rows; and dpages/reserved/used are replaced by the page columns in sys.allocation_units.

Aggregating across partitions

The crucial difference is the SUM with GROUP BY. Because a modern index can span many partitions, each contributes its own row in sys.partitions and its own allocation rows. The legacy dpages column simply returned wrong numbers here; the aggregate in this query rolls every partition up to a correct per-index total.

Joining allocation units correctly

sys.allocation_units joins to sys.partitions on partition_id = container_id. This captures all three allocation unit types — in-row data, row-overflow data, and LOB data — so the page totals include large-value storage that sysindexes.dpages ignored. Multiplying total_pages by 8 converts 8 KB pages to kilobytes; dividing by 1024 yields megabytes.

The shortcut: sys.dm_db_partition_stats

If you only need row and page counts, sys.dm_db_partition_stats exposes row_count, used_page_count, and reserved_page_count directly, collapsing the sys.partitions + sys.allocation_units join into a single DMV. It is the most concise modern equivalent of the old sysindexes size query:

 1SELECT
 2    OBJECT_SCHEMA_NAME(ps.object_id) AS schema_name,
 3    OBJECT_NAME(ps.object_id)        AS table_name,
 4    SUM(ps.row_count)                AS row_count,
 5    SUM(ps.reserved_page_count) * 8 / 1024.0 AS reserved_mb,
 6    SUM(ps.used_page_count)     * 8 / 1024.0 AS used_mb
 7FROM sys.dm_db_partition_stats AS ps
 8INNER JOIN sys.objects AS o
 9    ON ps.object_id = o.object_id
10WHERE o.is_ms_shipped = 0
11    AND o.type = 'U'
12    AND ps.index_id IN (0, 1)
13GROUP BY ps.object_id
14ORDER BY reserved_mb DESC;

The index_id IN (0, 1) filter restricts the row count to the heap or clustered index so each table is counted once, while the page columns still roll up all allocation. Where the legacy sysindexes query needed careful interpretation of dpages versus reserved versus used, this DMV labels each total plainly. Both forms — the explicit sys.allocation_units join and this DMV — are correct; pick the DMV when you want brevity and the join when you need to break totals down by allocation-unit type.

Key Benefits and Use Cases

  • Correct numbers on partitioned tables — the aggregate over sys.partitions avoids the silent inaccuracy of sysindexes.rowcnt and dpages.
  • Includes row-overflow and LOB pages — sys.allocation_units accounts for storage the legacy view omitted.
  • Future-proof — built on supported catalog views rather than a feature Microsoft has slated for removal.
  • Migration template — gives you a direct, column-by-column replacement to retrofit into old maintenance scripts.
  • Two valid forms — the explicit join for full control, or sys.dm_db_partition_stats for brevity.

Performance Considerations

  • sys.partitions.rows and DMV counts are approximate: they are maintained by the engine and not transactionally exact. They are excellent for trend reporting but not for reconciliation that requires an exact COUNT(*).
  • sys.sysindexes still resolves but should not be trusted: it works today for backward compatibility, yet returns inaccurate page counts with row-overflow data and incomplete results for partitioned and XML indexes.
  • Allocation-unit joins fan out: each partition can have multiple allocation units, so always aggregate; selecting raw rows without GROUP BY overcounts space.
  • Filter early: on a database with thousands of objects, restrict by schema or OBJECT_ID('schema.table') to keep the result set manageable.

Practical Tips

  • Grep legacy scripts for the tokens sysindexes, rowcnt, dpages, and indid — these are reliable markers of code that needs migration.
  • The old sysindexkeys table maps to sys.index_columns; use it to list the key columns of each index in order when modernizing index-documentation scripts.
  • For a quick one-liner row count of a single table, SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('Sales.Orders') AND index_id IN (0,1); is the modern replacement for the old sysindexes lookup.
  • When reporting space, decide whether you want reserved (allocated) or used pages — sysindexes blurred the distinction, but the modern views separate total_pages, used_pages, and data_pages cleanly.
  • Keep the migrated query in a DBA utility database so old habits do not creep back into new scripts.

Conclusion

sysindexes was the right answer two decades ago, but on any current SQL Server it is a deprecated compatibility view that returns misleading numbers for partitioned and large-value tables. The supported path — sys.indexes joined to sys.partitions and sys.allocation_units, or the all-in-one sys.dm_db_partition_stats — produces correct, partition-aware row and space totals and will keep working across upgrades. Treat any surviving sysindexes reference as technical debt and retire it with the patterns above.

References

Posts in this series