sys.indexes (Transact-SQL)

object_id

int

ID of the object to which this index belongs.

name

sysname

Name of the index. name is unique only within the object.

NULL = Heap

index_id

int

ID of the index. index_id is unique only within the object.

0 = Heap

1 = Clustered index

> 1 = Nonclustered index

type

tinyint

Type of index:

0 = Heap

1 = Clustered

2 = Nonclustered

3 = XML

4 = Spatial

type_desc

nvarchar(60)

Description of index type:

HEAP

CLUSTERED

NONCLUSTERED

XML

SPATIAL

is_unique

bit

1 = Index is unique.

0 = Index is not unique.

data_space_id

int

ID of the data space for this index. Data space is either a filegroup or partition scheme.

0 = object_id is a table-valued function.

ignore_dup_key

bit

1 = IGNORE_DUP_KEY is ON.

0 = IGNORE_DUP_KEY is OFF.

is_primary_key

bit

1 = Index is part of a PRIMARY KEY constraint.

is_unique_constraint

bit

1 = Index is part of a UNIQUE constraint.

fill_factor

tinyint

> 0 = FILLFACTOR percentage used when the index was created or rebuilt.

0 = Default value

is_padded

bit

1 = PADINDEX is ON.

0 = PADINDEX is OFF.

is_disabled

bit

1 = Index is disabled.

0 = Index is not disabled.

is_hypothetical

bit

1 = Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics.

0 = Index is not hypothetical.

allow_row_locks

bit

1 = Index allows row locks.

0 = Index does not allow row locks.

allow_page_locks

bit

1 = Index allows page locks.

0 = Index does not allow page locks.

has_filter

bit

1 = Index has a filter and only contains rows that satisfy the filter definition.

0 = Index does not have a filter.

filter_definition

nvarchar(max)

Expression for the subset of rows included in the filtered index.

NULL for heap or non-filtered index.