sys.index_columns (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Contains one row per column that is part of a sys.indexes index or unordered table (heap).

Column name Data type Description
object_id int ID of the object the index is defined on.
index_id int ID of the index in which the column is defined.
index_column_id int ID of the index column. index_column_id is unique only within index_id.
column_id int ID of the column in object_id.

0 = Row Identifier (RID) in a nonclustered index.

column_id is unique only within object_id.
key_ordinal tinyint Ordinal (1-based) within set of key-columns.

0 = Not a key column, or is an XML index, a columnstore index, or a spatial index.

Note: An XML or spatial index cannot be a key because the underlying columns are not comparable, meaning that their values cannot be ordered.
partition_ordinal tinyint Ordinal (1-based) within set of partitioning columns. A clustered columnstore index can have at most 1 partitioning column.

0 = Not a partitioning column.
is_descending_key bit 1 = Index key column has a descending sort direction.

0 = Index key column has an ascending sort direction, or the column is part of a columnstore or hash index.
is_included_column bit 1 = Column is a nonkey column added to the index by using the CREATE INDEX INCLUDE clause, or the column is part of a columnstore index.

0 = Column is not an included column.

Columns implicitly added because they are part of the clustering key are not listed in sys.index_columns.

Columns implicitly added because they are a partitioning column are returned as 0.

Permissions

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.

Examples

The following example returns all indexes and index columns for the table Production.BillOfMaterials.

USE AdventureWorks2012;  
GO  
SELECT i.name AS index_name  
    ,COL_NAME(ic.object_id,ic.column_id) AS column_name  
    ,ic.index_column_id  
    ,ic.key_ordinal  
,ic.is_included_column  
FROM sys.indexes AS i  
INNER JOIN sys.index_columns AS ic   
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id  
WHERE i.object_id = OBJECT_ID('Production.BillOfMaterials');  

Here is the result set.


index_name                                                 column_name        index_column_id key_ordinal is_included_column  
---------------------------------------------------------- -----------------  --------------- ----------- -------------  
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate ProductAssemblyID  1               1           0  
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate ComponentID        2               2           0  
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate StartDate          3               3           0  
PK_BillOfMaterials_BillOfMaterialsID                       BillOfMaterialsID  1               1           0  
IX_BillOfMaterials_UnitMeasureCode                         UnitMeasureCode    1               1           0  

(5 row(s) affected)  

See Also

Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
sys.indexes (Transact-SQL)
sys.objects (Transact-SQL)
CREATE INDEX (Transact-SQL)
sys.columns (Transact-SQL)
Querying the SQL Server System Catalog FAQ