sql - 如何以编程方式确定索引列是 ASC 还是 DESC

Question

以下查询创建了数据库中所有索引名称的列表，其中每一列都是该索引的一部分。谁能告诉我如何确定该列是按 ASC 排序还是按 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  

谢谢!马特

Answer 1

表格sys.index_columns有一列 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.