SQL Index Usage Stats

To find usage of Index on a table, insert your table name 'MyTable' in below script and execute.

DECLARE @varTable_Name SYSNAME = 'MyTable'

SELECT  object_name = OBJECT_NAME(i.object_id)

        , index_name = i.name

        , ius.user_seeks

        , ius.user_scans

        , ius.user_lookups

        , ius.user_updates

        , ius.last_user_seek

        , ius.last_user_scan

        , ius.last_user_lookup

        , ius.last_user_update  

FROM    sys.dm_db_index_usage_stats ius

JOIN    sys.indexes i

    ON    ius.object_id = i.object_id

AND    ius.index_id = i.index_id

WHERE   ius.database_id = DB_ID()

AND     OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1

AND     i.index_id <> 0

and        OBJECT_NAME(i.object_id) = @varTable_Name

select DB_NAME(database_id) AS [Database_Name],

OBJECT_NAME(ios.[object_id]) AS [Table_Name],

i.name AS [Index_Name],

ios.index_id,

leaf_insert_count, 

leaf_delete_count,

leaf_update_count

from sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID(@varTable_Name), NULL,NULL ) ios

inner join sys.indexes i

on ios.[object_id] = i.[object_id] and ios.index_id = i.index_id

exec sp_helpindex @varTable_Name

go