对于如何获取索引信息,想必大家对sp_helpindex并不陌生,这也是常用方法,但是它并不能提供包含列以及filter信息,于是乎尝试着写了如下SQL code,当然和大神们写的查询的sp不能相提并论,但是凑合着使用应该没有问题。
SELECT index_name,
index_description,
(LEFT(ind_col, LEN(ind_col)-1)
+ case when include_col IS NOT NULL
THEN ' INCLUDE (' + LEFT(include_col, LEN(include_col)-1) + ')'
else '' end) AS index_keys
FROM
(SELECT i.name AS index_name,
(SELECT CONVERT(varchar(max),
case when i.index_id = 1 then 'clustered' else 'nonclustered' end
+ case when i.ignore_dup_key <>0 then ', ignore duplicate keys' else '' end
+ case when i.is_unique <>0 then ', unique' else '' end
+ case when i.is_hypothetical <>0 then ', hypo