--- Below query is used to find the Index Details in a Database
select DB_NAME(a.database_id) DBName,OBJECT_NAME(a.object_id) Obj_Name,
*
from
sys.dm_db_index_physical_stats(db_id('mywork'),NULL,NULL,NULL,'LIMITED') As a
GO
--- Below query is used to find the Detailed Index Details in a Database
select DB_NAME(a.database_id) DBName,OBJECT_NAME(a.object_id) Obj_Name,i.name,
a.avg_fragmentation_in_percent,a.fragment_count,a.avg_fragment_size_in_pages,
a.page_count,a.record_count,a.avg_page_space_used_in_percent,
a.index_id,a.index_depth,a.index_level,
a.partition_number,a.index_type_desc,a.alloc_unit_type_desc,
a.min_record_size_in_bytes,a.max_record_size_in_bytes,
a.avg_record_size_in_bytes,a.compressed_page_count
from
sys.dm_db_index_physical_stats(db_id('mywork'),NULL,NULL,NULL,'DETAILED') As a
JOIN sys.indexes AS i
ON a.object_id = i.object_id AND a.index_id = i.index_id
GO
--- DMV to check the Index Usage Statistics
select DB_NAME(database_id) DBName,OBJECT_NAME(object_id) Obj_Name,
* from sys.dm_db_index_usage_stats
where DB_NAME(database_id)='mywork'
order by last_user_scan desc
GO
