usql.index_columns (U-SQL)
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);