SQL语句实现查询Index使用状况

SELECT sch.name + "." + t.name AS [Table Name], i.name AS[Index Name], i.type_desc,ISNULL（user_updates,0） AS [Total Writes], ISNULL（user_seeks +user_scans + user_lookups,0） AS [Total Reads], s.last_user_seek, s.last_user_scan , s.last_user_lookup, ISNULL（user_updates,0） - ISNULL（（user_seeks+ user_scans +user_lookups）,0）AS [Difference],p.reserved_page_count * 8.0 / 1024 as SpaceInMBFROM sys.indexes AS i WITH （NOLOCK）LEFT OUTERJOIN sys.dm_db_index_usage_statsAS s WITH （NOLOCK） ON s.object_id = i.object_id AND i.index_id = s.index_id AND s.database_id=db_id（） AND objectproperty（s.object_id,"IsUserTable"） = 1INNER JOINsys.tables AS t WITH （NOLOCK） ON i.object_id = t.object_idINNER JOINsys.schemas AS sch WITH （NOLOCK） ON t.schema_id = sch.schema_idLEFT OUTERJOIN sys.dm_db_partition_stats AS p WITH （NOLOCK） ON i.index_id = p.index_id and i.object_id = p.object_idWHERE （1=1） --AND ISNULL（user_updates,0） >=ISNULL（（user_seeks + user_scans + user_lookups）,0） --shows all indexesincluding those that have not been used--AND ISNULL（user_updates,0） -ISNULL（（user_seeks + user_scans + user_lookups）,0）>0 --only shows thoseindexes which have been used--AND i.index_id > 1-- Only non-first indexes （I.E.non-primary key） --AND i.is_primary_key<>1 -- Only those that are not defined asa Primary Key） --AND i.is_unique_constraint<>1-- Only those that are not classed as "UniqueConstraints". ORDER BY [Table Name],[index name]