usql.index_columns (U-SQL)


Contains one row per column for the indices of the schemas for the current database context.


Note that some of the values documented below, such as the values for non-clustered indices, column-store indices, or included columns, are there for possible future use and are not currently used.

Column name Data type Description
object_id_guid Guid Identifier of the object on which the index is defined
index_id int Ordinal position (starting at 1) of the index within the object/table
index_column_id int Position of the index column within the index (unique within the index_id)
column_id int Position of the column within the object on which the index is specified (unique within object_id_guid) or 0 if it is the row identifier (RID) in a nonclustered index
key_ordinal int Ordinal (1-based) within the set of key-columns

0 = Not a key column, or it is a columnstore index
is_descending_key bool True = Index key column has a descending sort direction

False = Index key column has an ascending sort direction, or the column is part of a columnstore or hash index
is_included_column bool True = Column is a non-key column added to the index as an included column

False = Column is not an included column


The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.

Query the usql.index_columns view

USE TestReferenceDB;

OUTPUT usql.index_columns
TO "/ReferenceGuide/CatalogViews/index_columns.txt"
USING Outputters.Tsv(outputHeader:true);

Query the usql.index_columns view with other catalog views

@index_columns =
    SELECT AS indexName,
   AS objectName,
   AS columnName,
           ic. *
    FROM usql.index_columns AS ic
    JOIN usql.indexes AS i
    ON ic.object_id_guid == i.object_id_guid
    JOIN usql.objects AS o
    ON i.object_id_guid == o.object_id_guid
    JOIN usql.columns AS c
    ON i.object_id_guid == c.object_id_guid
    AND ic.column_id == c.column_id;

OUTPUT @index_columns
TO "/ReferenceGuide/CatalogViews/index_columns_others.txt"
USING Outputters.Tsv(outputHeader:true); 

See Also