-- Get all SQL Statements with "table scan" in cached query plan 
;WITH  
 XMLNAMESPACES 
    (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'   
            ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)  
,EQS AS 
    (SELECT EQS.plan_handle 
           ,SUM(EQS.execution_count) AS ExecutionCount 
           ,SUM(EQS.total_worker_time) AS TotalWorkTime 
           ,SUM(EQS.total_logical_reads) AS TotalLogicalReads 
           ,SUM(EQS.total_logical_writes) AS TotalLogicalWrites 
           ,SUM(EQS.total_elapsed_time) AS TotalElapsedTime 
           ,MAX(EQS.last_execution_time) AS LastExecutionTime 
     FROM sys.dm_exec_query_stats AS EQS 
     GROUP BY EQS.plan_handle)    
SELECT EQS.[ExecutionCount] 
      ,EQS.[TotalWorkTime] 
      ,EQS.[TotalLogicalReads] 
      ,EQS.[TotalLogicalWrites] 
      ,EQS.[TotalElapsedTime] 
      ,EQS.[LastExecutionTime] 
      ,ECP.[objtype] AS [ObjectType] 
      ,ECP.[cacheobjtype] AS [CacheObjectType] 
      ,DB_NAME(EST.[dbid]) AS [DatabaseName] 
      ,OBJECT_NAME(EST.[objectid], EST.[dbid]) AS [ObjectName] 
      ,EST.[text] AS [Statement]       
      ,EQP.[query_plan] AS [QueryPlan] 
FROM sys.dm_exec_cached_plans AS ECP 
     INNER JOIN EQS 
         ON ECP.plan_handle = EQS.plan_handle      
     CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST 
     CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP 
WHERE EQP.[query_plan].exist('data(//RelOp[@PhysicalOp="Table Scan"][@EstimateRows * @AvgRowSize > 50000.0][1])') = 1 
      -- Optional filters 
      AND EQS.[ExecutionCount] > 1  -- No Ad-Hoc queries 
      AND ECP.[usecounts] > 1 
ORDER BY EQS.TotalElapsedTime DESC 
        ,EQS.ExecutionCount DESC;

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

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