Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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

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.

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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.