Description:

"Table scan" (and also "Index scan") can cause poor performance, especially when they are performed on large tables.
To identify queries causing such scans you can use the SQL Profiler with the events "Scans" => "Scan:Started" and "Scan.Stopped".
Other option is to analyse the cached query plans.
This Transact-SQL statements filters the cached query plans for existing table scan operators and returns the statement and query statistics.
An additional filter is set on the attribute "EstimateRows * @AvgRowSize" = "Estimate size" to filter out scans on small tables.
Note: The xml data of the cached query plans is not indexed in the DMV, therefore the query can run up to several minutes.

Works with SQL Server 2005 and higher versions in all editions.
Requires VIEW SERVER STATE permissions.