sys.index_columns (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
Contains one row per column that is part of an 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 one 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 non-key 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're part of the clustering key are not listed in sys.index_columns .Columns implicitly added because they're a partitioning column are returned as 0 . |
column_store_order_ordinal | tinyint | Applies to: Azure Synapse Analytics and SQL Server 2022 (16.x) Ordinal (1-based) within set of order columns in an ordered clustered columnstore index. For more on ordered clustered columnstore indexes, see Columnstore index design guidance. |
Permissions
The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was 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 AdventureWorks2022;
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)