如何在SQL Server中查看复合唯一约束/索引

Question

使用以下命令在表上创建了一个复合索引：

CREATE UNIQUE NONCLUSTERED INDEX example_index 
ON example_table (column1, column2, column3) WITH (IGNORE_DUP_KEY = OFF);

创建索引后，如何使用SQL语句查看索引及其特定列？

Answer 1

这是一个小技巧，所以这里有一个例子：

select schema_name(t.schema_id) schema_name,
t.name table_name,
i.name index_name,
i.is_unique,
i.is_primary_key,
c.name column_name,
ic.key_ordinal      
from sys.tables t
join sys.indexes i
on t.object_id = i.object_id
join sys.index_columns ic
on ic.object_id = i.object_id
and ic.index_id = i.index_id
join sys.columns c
on ic.object_id = c.object_id
and ic.column_id = c.column_id
where ic.is_included_column = 0
and t.type = 'U'
order by t.name, i.name, ic.key_ordinal

Answer 2

与所有SQL对象一样，您可以从系统表和/或模式视图中进行SELECT。