查看當前數據庫索引情況

本文主要是介绍查看當前數據庫索引情況，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

--以下SQL2005以上版本實現方法:with Index1
as
(
select top 100 percent row_number()over(partition by a.Name order by b.index_id) as ID,object_Name(a.object_id) as TableName,a.Name as IndexName,c.Name as ColName,description=a.type_desc,a.is_unique,a.is_primary_key,a.is_unique_constraint,OrderNr=CASE WHEN b.is_descending_key=0 THEN 'Asc' ELSE 'DESC' ENDfrom sys.indexes a 
joinsys.index_columns b on a.Object_id=b.Object_id and a.index_id=b.index_id 
joinsys.columns c on c.object_id=a.object_id and c.column_id=b.column_id
where objectproperty(a.object_id,'IsUserTable')=1 and a.Object_id<>object_id('dtproperties')
order by TableName,a.Name
)
,index2
as
(
select TableName,IndexName,ColName,is_unique,is_primary_key,is_unique_constraint,description
from (select  distinct TableName,IndexName,is_unique,is_primary_key,is_unique_constraint,description from Index1)a
CROSS apply(select ColName=stuff((select ','+ColName +' '+OrderNr from Index1 where TableName=a.TableName and IndexName=a.IndexName order by ID for xml PATH('')),1,1,''))b
)
select * 
from index2
order by TableName,IndexName

这篇关于查看當前數據庫索引情況的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---