Great!!! this script works for me - and I use the query plan column from the output which opens up with SSMS and I am able to find which query exactly using the index - note the query plan for procedure, function calls gives the entire query plan - however it quite easy to locate the object. with SQL Sentry Plan explorer it becomes easy to locate the index object. Another thing I have noticed is the query returns all the queries that impact the index, DML's impacting the indexes is also listed, but still it helps to understand the extra load during DML's on the index :).

The seek count and other extracts were not correct though for which I used

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],

I.[NAME] AS [INDEX NAME],

USER_SEEKS,

USER_SCANS,

USER_LOOKUPS,

USER_UPDATES

FROM SYS.DM_DB_INDEX_USAGE_STATS AS S

INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID

WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

AND S.database_id = DB_ID()

AND i.name = 'MYINDEX'

Also my thanks to Vinay bringing up the question I had in mind.... 🙂