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.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	Index size and fragmentation statistics.
sys.dm_db_index_operational_stats	Current index and table I/O statistics.
sys.dm_db_index_usage_stats	Index usage statistics by query type.
sys.dm_db_missing_index_columns	Information about database table columns that are missing an index.
sys.dm_db_missing_index_details	Detailed information about missing indexes.
sys.dm_db_missing_index_group_stats	Summary information about groups of missing indexes
sys.dm_db_missing_index_groups	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.

See Also

Concepts

Indexes

Help and Information

Getting SQL Server 2005 Assistance