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

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

Three catalog views, sys.objects, sys.tables, and sys.indexes, answer most day-to-day questions about what a database contains. This script joins them with sys.schemas and sys.partitions into one inventory: every user table, its schema, its dates, its row count, and each index defined on it. The row count comes from a per-table subquery, so nonclustered index rows repeat the table's figure instead of showing NULL.

Purpose and Overview

sys.objects is the base catalog view for schema-scoped objects: it contains a row for each user-defined, schema-scoped object created within a database, identified by an object_id that is unique within that database. One exclusion matters when you inventory triggers: sys.objects does not show DDL triggers because they are not schema-scoped. All triggers, DML and DDL, are found in sys.triggers.

sys.tables is a derived view. The pair relates like this: "The sys.tables catalog view returns the columns that are specific to tables and also all the columns that the sys.objects catalog view returns." After a table is created, its metadata appears in both views, but there is only one metadata entry with one name and one object_id. In catalog-view terms, sys.objects is a base view with a subset of columns and a superset of rows, and sys.tables is a derived view with a superset of columns and a subset of rows. sys.indexes then holds a row per index or heap of a tabular object, tied back to the table by object_id.

Because all three views share the object_id key, they join without any translation. The script below uses that to build a per-index inventory that works as a starting point for capacity reviews, index audits, and database documentation. Catalog views are the preferred route because they are "the most general interface to the catalog metadata".

The sys views sit beside the INFORMATION_SCHEMA data-dictionary queries, and they replace the SQL Server 2000 system tables covered in sysindexes vs sys.indexes; sys.sysindexes is only a backward-compatibility view, and the current system views are the ones to use instead. For index type, fill factor, or partition data, the sys views carry the columns: sys.indexes exposes type_desc and fill_factor, and sys.partitions exposes data_compression_desc.

Code Breakdown

The query lists every user table with its indexes and a table-level row count. 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    tr.row_count,
 8    i.index_id,
 9    i.name                          AS index_name,
10    i.type_desc                     AS index_type,
11    i.is_primary_key,
12    i.is_unique,
13    i.is_disabled
14FROM sys.tables AS t
15INNER JOIN sys.schemas AS s
16    ON t.schema_id = s.schema_id
17INNER JOIN sys.indexes AS i
18    ON t.object_id = i.object_id
19OUTER APPLY (
20    SELECT SUM(p.rows) AS row_count
21    FROM sys.partitions AS p
22    WHERE p.object_id = t.object_id
23      AND p.index_id IN (0, 1)
24) AS tr
25WHERE t.is_ms_shipped = 0
26  AND i.is_hypothetical = 0
27ORDER 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 returns "a row for each user table", so no type = 'U' filter is needed. The predicate t.is_ms_shipped = 0 then leaves out objects created by an internal SQL Server component, which is how that column is defined. create_date and modify_date are inherited from sys.objects. modify_date is the date the object was last modified by an ALTER statement, and for a table or view it also changes when an index on it is created or altered. Treat it as a hint that something about the table changed, not as proof that the column list changed.

Resolving the schema with sys.schemas

sys.tables exposes schema_id, an integer. Joining to sys.schemas turns it into a readable name. sys.schemas has one row per database schema, with name and schema_id both unique within the database, and it requires only membership in the public role. The SCHEMA_NAME(t.schema_id) function would also work. The explicit join keeps the relationship visible and lets you filter or sort on other schema columns, such as the owning principal_id, without a second lookup.

Indexes and heaps from sys.indexes

The join to sys.indexes produces one row per index. index_id is the discriminator: 0 is a heap, 1 is a clustered index, and anything greater than 1 is a nonclustered index. type_desc spells the same thing out as HEAP, CLUSTERED, NONCLUSTERED, and the other index types (XML, spatial, columnstore, and so on). is_primary_key and is_unique_constraint mark indexes that back constraints, and is_unique marks unique indexes. For a heap, name is NULL, so the heap row appears with no index name. The is_hypothetical = 0 predicate drops hypothetical indexes, which are indexes that "can't be used directly as a data access path" and that hold only column-level statistics. is_disabled is returned so a disabled index shows up instead of hiding among the live ones.

Row counts from sys.partitions

sys.indexes stores no row counts, so the script reads sys.partitions. That view holds one row for each partition of all the tables and most types of indexes in the database, and every table and index has at least one partition, whether or not it is explicitly partitioned. The rows column is "the approximate number of rows in this partition".

Two details shape the subquery. First, the heap (index_id 0) or the clustered index (index_id 1) carries the table's data rows, so the filter p.index_id IN (0, 1) selects the right partitions. Second, a partitioned table has several partition rows for the same index, so the subquery sums them with SUM(p.rows) and returns one number per table. Putting that filter in the ON clause of a plain LEFT JOIN to sys.partitions would leave the count NULL on every nonclustered index row, and a partitioned table would produce one output row per partition. OUTER APPLY avoids both problems, and a table with no partition rows would still appear with a NULL count.

Key Benefits and Use Cases

  • Single-query inventory. Every user table, its schema, and all its indexes in one result set that exports cleanly to documentation or a spreadsheet.
  • Index-audit starting point. Rows with index_type = 'HEAP' show tables without a clustered index, and a quick GROUP BY on table_name shows tables that carry many nonclustered indexes.
  • Capacity review. Approximate row counts per table, without running sp_spaceused one table at a time.
  • Change tracking. create_date and modify_date give a cheap way to find tables touched since the last release.
  • Disabled-index check. is_disabled surfaces indexes that a maintenance job or deployment left switched off.
  • Foundation for richer scripts. The object_id join pattern extends to sys.columns, sys.index_columns, and the rest of the object catalog views.

Performance Considerations

  • Catalog views are metadata reads. They are cheap compared with scanning user data, but a database with tens of thousands of objects returns a large index-level result set, so filter by schema or table when you only need part of it.
  • sys.partitions.rows is approximate. It is "the approximate number of rows in this partition". For an exact figure on one table, COUNT(*) against the table returns the real count, at the cost of reading it.
  • Avoid SELECT * against catalog views. New columns may be added to the end of any system catalog view, so SELECT * FROM sys.<catalog_view_name> can break a production application. This script names every column it returns.
  • Metadata visibility is permission-scoped. The visibility of metadata in catalog views is limited to securables that a user either owns or has been granted some permission on. The same query can return different rows for different logins, and an empty result for a low-privilege account is not proof that the database is empty.
  • The catalog has gaps by design. Catalog views do not contain information about replication, backup, database maintenance plan, or SQL Server Agent catalog data. Those need their own queries.
  • Database scope. Each view describes objects within a database, so run the script in the context of each database you want to inventory.

Practical Tips

  • To include views and functions alongside tables, start from sys.objects instead of sys.tables and filter on the type column. Use U for a user table and V for a view. Join sys.indexes the same way; it covers tabular objects such as a table, view, or table-valued function.
  • Add sys.columns, joined on object_id, to report column counts. It has a row for each column of an object that has columns, such as views or tables, and column IDs might not be sequential.
  • Join sys.index_columns on object_id and index_id to list the key columns of each index. Its key_ordinal column gives the 1-based position in the key, and is_included_column separates INCLUDE columns from key columns.
  • To find heaps, filter the result on i.index_id = 0.
  • To find tables with no primary key, the usual check is OBJECTPROPERTY(object_id, 'TableHasPrimaryKey') = 0. The companion post on finding tables without primary keys covers it.
  • Capture the result into a reporting table on a schedule and compare snapshots over time to track schema drift and table growth across releases.

Conclusion

sys.objects, sys.tables, and sys.indexes carry the core of SQL Server's object metadata, and the shared object_id makes a complete table-and-index inventory one set of joins. Summing sys.partitions per table keeps the row count honest on partitioned tables, and sticking to named columns protects the script from future column additions. Keep it as the first query to run when you need to know what a database actually contains.

References

Posts in this series