sys.dm_db_index_usage_stats

sys.dm_db_index_usage_stats is a SQL Server DMV . The recordset Returns information about  different types of index operations counts and last time operations occured.

select * from sys.dm_db_index_usage_stats


--will return a recordset , but we need to make slightly more useful.
--The following sql statement ,assumes the sql is executed on the db

DECLARE @dbid INT;

SELECT @dbid = db_id();

SELECT  objectName=object_name(s.object_id), s.object_id, indexName=i.name,indexType=i.type_desc
, user_seeks, user_scans, user_lookups, user_updates

FROM sys.dm_db_index_usage_stats s,

            sys.indexes i

WHERE database_id = @dbid AND objectproperty(s.object_id,'IsUserTable') = 1

AND i.object_id = s.object_id

AND i.index_id = s.index_id

ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC;

 Related Posts

Top 5 SQL Server DMV for Index Analysis

Find all DMV and DMF