1 out of 1 rated this helpful - Rate this topic

Viewing Index Information

SQL Server 2008

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.

Tasks

How to: View the Properties of an Index (SQL Server Management Studio)

Concepts

Indexes

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD