SQL Server Catalog Views: sys.tables, sys.indexes, sys.objects

calendar Jun 14, 2026 / · 6 min read · sql server catalog views system tables metadata sys.tables sys.indexes sys.objects sys.schemas sys.partitions sys.columns sys.index_columns sys.foreign_keys database structure scripts  ·
Share on: linkedin copy

The sys catalog views are the supported, forward-compatible way to read SQL Server metadata, and three of them — sys.objects, sys.tables, and sys.indexes — answer most day-to-day questions about what lives in a database. This script joins them into one inventory of every user table, its schema, its row count, and each index defined on it.

Purpose and Overview

sys.objects is the base catalog view: it contains one row for every user-defined, schema-scoped object in a database — tables, views, stored procedures, functions, constraints, triggers, and more — each identified by a unique object_id. sys.tables is a derived view built on top of it. It returns every column sys.objects returns, plus table-specific columns such as temporal_type and is_memory_optimized, but only for rows where the object is a user table. sys.indexes then carries one row per index or heap of each tabular object, keyed back to the table by object_id.

Understanding that inheritance is the key to querying these views well: the base view holds a superset of rows and a subset of columns, while the derived view holds a subset of rows and a superset of columns. Because all three share the same object_id namespace, they join cleanly. This script uses that to produce a per-index inventory — schema, table, creation and modification dates, row count, and the index name, type, and uniqueness flags — that serves as a starting point for capacity reviews, index audits, and database documentation.

These proprietary sys views are the SQL-Server-native counterpart to the ANSI-standard INFORMATION_SCHEMA data-dictionary queries, and the supported successor to the SQL Server 2000 system tables covered in sysindexes vs sys.indexes. When you need detail the ISO views don't expose — index type, fill factor, partition data — the sys views are the right tool.

Code Breakdown

The query below lists every user table with its indexes and row counts. Run it in the context of the database you want to inventory.

 1SELECT
 2    s.name                          AS schema_name,
 3    t.name                          AS table_name,
 4    t.object_id,
 5    t.create_date,
 6    t.modify_date,
 7    i.index_id,
 8    i.name                          AS index_name,
 9    i.type_desc                     AS index_type,
10    i.is_primary_key,
11    i.is_unique,
12    p.rows                          AS row_count
13FROM sys.tables AS t
14INNER JOIN sys.schemas AS s
15    ON t.schema_id = s.schema_id
16INNER JOIN sys.indexes AS i
17    ON t.object_id = i.object_id
18LEFT JOIN sys.partitions AS p
19    ON  i.object_id = p.object_id
20    AND i.index_id  = p.index_id
21    AND p.index_id IN (0, 1)
22WHERE t.is_ms_shipped = 0
23ORDER BY s.name, t.name, i.index_id;

The driving view: sys.tables

The query starts from sys.tables rather than sys.objects because it wants user tables only — sys.tables already filters to type = 'U' rows for you. The predicate t.is_ms_shipped = 0 then excludes objects created by internal SQL Server components, leaving the application schema. create_date and modify_date are inherited straight from sys.objects; note that modify_date advances not only on ALTER TABLE but also whenever an index on the table is created or altered.

Resolving the schema with sys.schemas

sys.tables exposes only schema_id, an integer. Joining to sys.schemas turns it into a readable schema name. You could substitute the SCHEMA_NAME(t.schema_id) function for the join, but the explicit join makes the relationship visible and lets you filter on schema in the WHERE clause if needed.

Indexes and heaps from sys.indexes

The join to sys.indexes produces one row per index. The index_id column is the discriminator: 0 is a heap (a table with no clustered index), 1 is the clustered index, and values greater than 1 are nonclustered indexes. The type_desc column spells this out as HEAP, CLUSTERED, NONCLUSTERED, and so on, while is_primary_key and is_unique flag the constraints an index enforces. A heap still produces a row here with a NULL index name.

Row counts from sys.partitions

sys.indexes does not store row counts, so the query reaches into sys.partitions, where the rows column gives an approximate count per partition. The join is restricted to p.index_id IN (0, 1) — the heap or the clustered index — because those represent the table's data rows; counting every nonclustered index partition would multiply the totals. It is a LEFT JOIN so that a table with no rows still appears.

Key Benefits and Use Cases

  • Single-query inventory — every user table, its schema, and all its indexes in one result set, ready to export as documentation.
  • Index-audit starting point — surfaces heaps (index_id = 0), missing clustered indexes, and tables carrying many nonclustered indexes.
  • Capacity review — approximate row counts per table without running sp_spaceused table by table.
  • Supported and forward-compatible — catalog views are the interface Microsoft commits to maintaining across versions, unlike direct system-table access.
  • Foundation for richer scripts — the object_id join pattern extends to sys.columns, sys.index_columns, sys.foreign_keys, and the rest of the catalog.

Performance Considerations

  • Catalog views are metadata reads: they query in-memory system structures and are inexpensive, but on databases with tens of thousands of objects the index-level join can return large result sets — filter by schema or table where you can.
  • sys.partitions.rows is approximate: it reflects the value the engine maintains, which can lag slightly behind reality and is not transactionally exact. For precise counts use COUNT(*), accepting the scan cost.
  • Avoid SELECT * against catalog views: Microsoft may add columns to the end of a catalog view in future releases, which can silently break code that depends on column position. Name the columns you need, as this script does.
  • Metadata visibility is permission-scoped: a user sees only objects they own or have some permission on, so the same query can return different rows for different principals.

Practical Tips

  • To include views and table-valued functions alongside tables, start the query from sys.objects instead and filter on type IN ('U','V'), joining sys.indexes the same way.
  • Add sys.columns (joined on object_id) to extend the inventory with column counts, or sys.index_columns to list the key columns of each index in order.
  • Filter to heaps with WHERE i.index_id = 0 to find tables lacking a clustered index — a common tuning target on OLTP systems.
  • Capture the result into a reporting table on a schedule and compare snapshots over time to track schema drift and table growth across releases.
  • Wrap the query in a view in a DBA utility database so the whole team queries metadata the same way.

Conclusion

sys.objects, sys.tables, and sys.indexes form the core of SQL Server's catalog-view layer, and the object_id key that ties them together makes a complete table-and-index inventory a single join away. Because catalog views are the supported, version-stable interface to metadata, scripts built on them keep working as servers are upgraded. Keep this query in your toolkit as the first stop whenever you need to know what a database actually contains.

References

Posts in this series