I would like to see information on how many times has a table been fully scanned.
I was hoping for something like sys.dm_db_index_usage_stats but for table scans.
There is a method described at the blog keepitsimleandfast, but I would rather get all scans since server started.
What I am looking for is also similiar to pg_stat_all_tables.seq_scan in PostgreSQL.
Well, that table will give you the answer you are looking for. Just join onto sys.indexes to determine the type of index. Tables are either Heaps or Clustered indexes, so a scan on that index type is a scan on the table. See the example below, add any additional columns you are looking for.
SELECT S.name AS SchemaName
, O.name AS ObjectName
, I.name AS IndexName
, I.type AS IndexType
, I.type_desc AS IndexType_Desc
, IUS.user_scans
FROM sys.objects AS O
INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id
INNER JOIN sys.indexes AS I ON I.object_id = O.object_id
INNER JOIN sys.dm_db_index_usage_stats AS IUS ON IUS.object_id = I.object_id AND IUS.index_id = I.index_id
WHERE I.type IN (0,1) -- 0=HEAP, 1=CLUSTERED (table)
