sys.index_columns (Transact-SQL)sys.index_columns (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

每一部分的列中对应一行sys.indexes索引或未排序的表 (堆)。Contains one row per column that is part of a sys.indexes index or unordered table (heap).

列名Column name 数据类型Data type 描述Description
object_idobject_id intint 在定义索引的对象 ID。ID of the object the index is defined on.
index_idindex_id intint 定义了列的索引的 ID。ID of the index in which the column is defined.
index_column_idindex_column_id intint 索引列的 ID。ID of the index column. index_column_id仅中是唯一index_idindex_column_id is unique only within index_id.
column_idcolumn_id intint 中列的 ID object_idID of the column in object_id.

0 = 非聚集索引中的行标识符 (RID)。0 = Row Identifier (RID) in a nonclustered index.

column_id仅中是唯一object_idcolumn_id is unique only within object_id.
key_ordinalkey_ordinal tinyinttinyint 键列集内的序数(从 1 开始)。Ordinal (1-based) within set of key-columns.

0 = 不是键列,或者是 XML 索引、列存储索引或空间索引。0 = Not a key column, or is an XML index, a columnstore index, or a spatial index.

注意:XML 索引或空间索引不能为键的基础列不是可比较,因为这意味着它们的值不能进行排序。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_ordinalpartition_ordinal tinyinttinyint 分区列集内的序数(从 1 开始)。Ordinal (1-based) within set of partitioning columns. 聚集列存储索引可以具有最多 1 个分区列。A clustered columnstore index can have at most 1 partitioning column.

0 = 非分区列。0 = Not a partitioning column.
is_descending_keyis_descending_key bitbit 1 = 索引键列采用降序排序。1 = Index key column has a descending sort direction.

0 = 索引键列的排序方向为升序,或者列是列存储或哈希索引的一部分。0 = Index key column has an ascending sort direction, or the column is part of a columnstore or hash index.
is_included_columnis_included_column bitbit 1 = 列是使用 CREATE INDEX INCLUDE 子句添加到索引的非键列,或者列是列存储索引的一部分。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 = 列不是包含列。0 = Column is not an included column.

隐式添加,因为它们是聚集键的一部分的列不列在sys.index_columnsColumns implicitly added because they are part of the clustering key are not listed in sys.index_columns.

由于是分区列而隐式添加的列作为 0 返回。Columns implicitly added because they are a partitioning column are returned as 0.
适用范围:Azure SQL 数据仓库 (预览版)Applies to: Azure SQL Data Warehouse (preview)
tinyinttinyint 序号 (从 1 开始) 中设置的有序的聚集列存储索引中列的顺序。Ordinal (1-based) within set of order columns in an ordered clustered columnstore index.


目录视图中仅显示用户拥有的安全对象的元数据,或用户对其拥有某些权限的安全对象的元数据。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. 有关详细信息,请参阅 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.


下例返回表 Production.BillOfMaterials 的所有索引和索引列。The following example returns all indexes and index columns for the table Production.BillOfMaterials.

USE AdventureWorks2012;  
SELECT AS index_name  
    ,COL_NAME(ic.object_id,ic.column_id) AS column_name  
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

对象目录视图 (Transact-SQL) Object Catalog Views (Transact-SQL)
目录视图 (Transact-SQL) Catalog Views (Transact-SQL)
sys.indexes (Transact-SQL) sys.indexes (Transact-SQL)
sys.objects (Transact-SQL) sys.objects (Transact-SQL)
sys.columns (Transact-SQL) sys.columns (Transact-SQL)
查询 SQL Server 系统目录常见问题解答Querying the SQL Server System Catalog FAQ