Full Scan, Index Scan Query

with XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)

SELECT * FROM

 (

 SELECT

  db_name(qt.dbid) AS 'DB Name'

  ,qt.dbid

  ,OBJECT_NAME(qp.objectid,qp.dbid) as sp_name

  ,qt.text AS 'sp_text'

  , substring(qt.text, (qs.statement_start_offset/2)+1

   , ((case qs.statement_end_offset

      when -1 then datalength(qt.text)

      else qs.statement_end_offset

      end - qs.statement_start_offset)/2) + 1) as statement_text

  , qs.creation_time

  , qs.execution_count AS 'Execution Count'

  , ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'

  , DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'

  , ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime(㎲)'

  , qs.total_elapsed_time/1000.0/1000.0 AS 'TotalElapsedTime(sec)'

  , max_elapsed_time /1000.0 AS 'maxelapsedTime(ms)'

  , qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime(㎲)'

  , qs.total_worker_time AS 'TotalWorkerTime(㎲)'

  , max_worker_time as 'max_worker_time(㎲)'

  , ISNULL(qs.total_logical_reads/qs.execution_count, 0) AS 'AvgLogicalreads'

  , total_logical_reads

  , qs.max_logical_reads

  , ISNULL(qs.total_physical_reads/qs.execution_count, 0) AS 'AvgphysicalReads'

  , total_physical_reads

  , qs.max_physical_reads

  , ISNULL(qs.total_logical_writes/qs.execution_count, 0) AS 'AvglogicalWrites'

  , qs.total_logical_writes

  , qs.max_logical_writes

  ,text

  ,cast(query_plan as xml) as query_plan

 FROM sys.dm_exec_query_stats as qs

 CROSS APPLY sys.dm_exec_sql_text(plan_handle) as qt

 CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,qs.statement_start_offset,qs.statement_end_offset) qp

 WHERE  db_name(qt.dbid) <> 'nxdba'

)Y

CROSS APPLY

(

      SELECT

       c.value('(./@PhysicalOp)[1]','varchar(100)') as PhysicalOp

       FROM Y.query_plan.nodes('//sql:RelOp')B(C)

)X

where PhysicalOp  IN ('Table Scan','Index Scan')and

Y.dbid not in (1,2,3,4,32767)