Table of contents
Collapse the table of content
Expand the table of content

usql.index_columns (U-SQL)

Michael Rys|Last Updated: 5/15/2018
4 Contributors

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 nameData typeDescription
object_id_guidGuidIdentifier of the object on which the index is defined
index_idintOrdinal position (starting at 1) of the index within the object/table
index_column_idintPosition of the index column within the index (unique within the index_id)
column_idintPosition 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_ordinalintOrdinal (1-based) within the set of key-columns

0 = Not a key column, or it is a columnstore index
is_descending_keyboolTrue = 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_columnboolTrue = 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

© 2018 Microsoft