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 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. |
Permissions
In SQL Server 2005 and later versions, the visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.
See Also