How do I Programatically Determine if an Index Column is ASC or DESC

Question

The following query creates a list of all index names in the database a long with each column that is part of that index. Can someone tell me how to determine if the column is sorted ASC or DESC? ``` SELECT ind.name as index_name , t.[name] as table_name , col.name as column_name , ic.index_column_id as index_column_id FROM [GDI-193-DEV].sys.indexes ind INNER JOIN [GDI-193-DEV].sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id INNER JOIN [GDI-193-DEV].sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id INNER JOIN [GDI-193-DEV].sys.tables t ON ind.object_id = t.object_id WHERE ind.is_primary_key = 0 AND ind.is_disabled = 0 ORDER BY t.name, ind.name, ind.index_id, ic.index_column_id ``` Thanks! Matt

Accepted Answer

Table `sys.index_columns` has a column `is_descending_key`

1 = Index key column has a descending sort direction. 0 = Index key column has an ascending sort direction. Does not apply to columnstore indexes which return 0.

How do I Programatically Determine if an Index Column is ASC or DESC

Solution

Problem