sql - 我们如何在 SQL Server 中使用 sysindexes 表获取表索引和相关列？

Question

我想在我的表中获取已定义的索引和相关列。我可以做到这一点

sp_helpindex 'tableName'

但我想分别获得唯一和非唯一索引。我们如何通过在 sysindexes 表上编写查询来做到这一点？

Answer 1

sys.indexes View 有一列 is_unique:

select  i.name as IndexName
,       ic.key_ordinal as IndexColumnPosition
,       c.name as IndexColumnName
from    sys.indexes i
left join
        sys.index_columns ic
on      ic.object_id = i.object_id
        and ic.index_id = i.index_id
left join
        sys.columns c
on      c.object_id = ic.object_id
        and c.column_id = ic.column_id
where   i.object_id = object_id('YourTable')
        and i.is_unique = 1