SELECT 
			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 = @dbid
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.
ORDER BY (s.user_seeks+s.user_scans+s.user_lookups+s.user_updates) asc

	select  object_name(objectid),
			query_plan.query('count[//@Index=''[IDX_SettledMonthToDate]'']') as result,
			query_plan
	from sys.dm_exec_cached_plans pl
	cross apply sys.dm_exec_query_plan(pl.plan_handle) 
	where dbid = 14 

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
	DB_NAME(E.dbid) AS [DBName],
	object_name(E.objectid, dbid) AS [ObjectName],
	P.cacheobjtype AS [CacheObjType],
	P.objtype AS [ObjType],
	E.query_plan.query('count(//RelOp[@LogicalOp = ''Index Scan'' or @LogicalOp = ''Clustered Index Scan'']/*/Object[@Index=''[MyIndex]''])') AS [ScanCount],
	E.query_plan.query('count(//RelOp[@LogicalOp = ''Index Seek'' or @LogicalOp = ''Clustered Index Seek'']/*/Object[@Index=''[MyIndex]''])') AS [SeekCount],
	E.query_plan.query('count(//Update/Object[@Index=''[MyIndex]''])') AS [UpdateCount],
	P.refcounts AS [RefCounts],
	P.usecounts AS [UseCounts],
	E.query_plan AS [QueryPlan]
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_query_plan(P.plan_handle) E
WHERE
	E.dbid = DB_ID('MyDatabase') AND
	E.query_plan.exist('//*[@Index=''[MyIndex]'']') = 1