Share via

  • Article

Viewing Index Information

There are several catalog views and functions that display index metadata information. For example, you can display the types of indexes that exist on a particular table, the current index options set for a specified index, or the total space used by one or more indexes in the database.

Index Catalog Views

The following table lists the catalog views that return index metadata.

Catalog view

Displays information about

sys.indexes

Index type, filegroup or partition scheme id, and the current setting of index options that are stored in metadata.

sys.index_columns

Column ID, position within the index, type (key or nonkey) and sort order (ASC or DESC).

sys.spatial_index_tessellations

Type and tessellation scheme of each spatial index, as well as basic information about each index.

sys.spatial_indexes

Information on what tessellation scheme and parameters have been used for the spatial indexes.

sys.stats

Statistics associated with an index including statistics name, and whether it was created automatically or user created.

sys.stats_columns

Column ID associated with the statistic.

sys.xml_indexes

XML index type, primary or secondary, and the secondary type and description.

Index Functions

These system functions also return index metadata.

Function

Displays information about

sys.dm_db_index_physical_stats (Transact-SQL)

Index size and fragmentation statistics.

sys.dm_db_index_operational_stats (Transact-SQL)

Current index and table I/O statistics.

sys.dm_db_index_usage_stats (Transact-SQL)

Index usage statistics by query type.

sys.dm_db_missing_index_columns (Transact-SQL)

Information about database table columns that are missing an index.

sys.dm_db_missing_index_details (Transact-SQL)

Detailed information about missing indexes.

sys.dm_db_missing_index_group_stats (Transact-SQL)

Summary information about groups of missing indexes

sys.dm_db_missing_index_groups (Transact-SQL)

Information about what missing indexes are contained in a specific missing index group.

INDEXKEY_PROPERTY

Index column position within the index and column sort order (ASC or DESC).

INDEXPROPERTY

Index type, number of levels, and current setting of index options that are stored in metadata.

INDEX_COL

The name of the key column of the specified index.