usql.index_columns (U-SQL)

  • Article

Summary

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

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 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

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