Queries, Plans, and Indexes

/*

This query gets all queries, their query plans, and all indexes included in those plans.


Authors:            Eric Z. Beard, eric@loopfx.com, ericzbeard@yahoo.com
                        (borrowed some stuff from http://mohammedu.spaces.live.com/Blog/cns!6699CF8ADD3D4F67!325.entry, 
                            Greg Lindwood? Uma Chandran?)
Date of Origin:        July, 2008

*/
use master;

;with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as s)

select 
    st.text
,    qp.query_plan
,    qp_objects.qp_obj_schema
,    qp_objects.qp_obj_table
,    qp_objects.qp_obj_index
from (
 select s.sql_handle, s.plan_handle, max(s.execution_count) as ec, max(s.total_logical_reads) as tlr
 from sys.dm_exec_query_stats as s
 group by s.sql_handle, s.plan_handle
) as qs
cross apply sys.dm_exec_query_plan(qs.plan_handle) as qp
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply ( 
    select 
            obj_nodes.value('@Schema', 'nvarchar(130)') as qp_obj_schema 
        ,    obj_nodes.value('@Table', 'nvarchar(130)') as qp_obj_table 
        ,    obj_nodes.value('@Index', 'nvarchar(130)') as qp_obj_index 
    from qp.query_plan.nodes('//s:Object[@Schema != "[sys]" and @Index and local-name(parent::node()) != "Update"]') as qry_plan(obj_nodes) 
) as qp_objects
;