IndexHitSQLServer.sql

SELECT	@@SERVERNAME as ServerName,
         OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
         INDEXNAME = I.NAME,
         I.INDEX_ID,
         S.*
FROM 
      SYS.INDEXES I
      INNER JOIN SYS.OBJECTS O
         ON I.OBJECT_ID = O.OBJECT_ID
      INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S
         ON   S.OBJECT_ID = I.OBJECT_ID
               AND I.INDEX_ID = S.INDEX_ID
               AND DATABASE_ID = DB_ID(db_name()) 
WHERE 
      OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
      AND I.NAME IS NOT NULL and
      OBJECT_NAME(I.OBJECT_ID) not like 'sys%' 
      AND I.NAME not like 'PK%'    -- Ignore Pk's
      and I.NAME not like 'UC%'    -- Ignore unique constraints.
	  --and OBJECT_NAME(I.OBJECT_ID) = '<table name>'
ORDER BY (s.user_seeks+s.user_scans+s.user_lookups+s.user_updates) asc