sql-server - 我们如何在 SQL Server 中查看索引的列

Question

我正在尝试查看特定索引的列，但没有成功并感谢任何帮助。

select * from sys.indexes 
inner join sys.index_columns on sys.indexes.object_id = sys.index_columns.object_id
inner join sys.columns on sys.index_columns.column_id = sys.columns.column_id 
where sys.indexes.name = 'IndexName'

标签： sql-server

Answer 1

总结了答案中的所有评论。

样本数据

create table MyTable
(
  Col1 int,
  Col2 int,
  Col3 int
);

create clustered index MyIdx1 on MyTable (Col1);

create nonclustered index MyIdx2 on MyTable (Col2);

create nonclustered index MyIdx3 on MyTable (Col2) include (Col3);

解决方案

select i.object_id as table_id,
       object_name(i.object_id) as table_name,
       i.index_id,
       i.name as index_name,
       i.type_desc as index_type,
       c.column_id,
       c.name as column_name,
       ic.is_included_column as column_included
from sys.indexes i
join sys.index_columns ic
  on  ic.object_id = i.object_id
  and ic.index_id = i.index_id
join sys.columns c
  on  c.object_id = ic.object_id
  and c.column_id = ic.column_id
where i.name like 'MyIdx%';

结果

table_id    table_name  index_id    index_name      index_type      column_id   column_name column_included
----------- ----------- ----------- --------------- --------------- ----------- ----------- ---------------
581577110   MyTable     1           MyIdx1          CLUSTERED       1           Col1        False
581577110   MyTable     2           MyIdx2          NONCLUSTERED    2           Col2        False
581577110   MyTable     3           MyIdx3          NONCLUSTERED    2           Col2        False
581577110   MyTable     3           MyIdx3          NONCLUSTERED    3           Col3        True

小提琴