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 |
|---|---|
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). |
|
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. |
Index Functions
These system functions also return index metadata.
| Function | Displays information about |
|---|---|
Index size and fragmentation statistics. |
|
Current index and table I/O statistics. |
|
Index usage statistics by query type. |
|
Information about database table columns that are missing an index. |
|
Detailed information about missing indexes. |
|
Summary information about groups of missing indexes |
|
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. |