Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

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.

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

Catalog view

Displays information about


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


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


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


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


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


Column ID associated with the statistic.


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

These system functions also return index metadata.


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.


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


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


The name of the key column of the specified index.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft