Share via


sys.indexes (Transact-SQL)

Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.

Column name Data type Description

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

type_desc

nvarchar(60)

Description of index type:

HEAP

CLUSTERED

NONCLUSTERED

XML

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.

See Also

Reference

Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
sys.index_columns (Transact-SQL)
sys.xml_indexes (Transact-SQL)
sys.objects (Transact-SQL)
sys.key_constraints (Transact-SQL)
sys.filegroups (Transact-SQL)
sys.partition_schemes (Transact-SQL)

Other Resources

Querying the SQL Server System Catalog FAQ

Help and Information

Getting SQL Server 2005 Assistance