usql.index_columns (U-SQL)

 

Updated: September 26, 2017

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

System_CAPS_ICON_note.jpg Note

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

Examples

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 i.name AS indexName,
            o.name AS objectName,
            c.name 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

Community Additions

ADD
Show: