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