Here’s a quick query that tells you how many rows have been changed (table & index) since the last statistics update.
Select OBJECT_NAME(I.id) As TableName
,Sum(Convert(BigInt,I.rowmodctr)) As RowsModSinceStats
,C.RowCnt
From sys.sysindexes As I
Left
Outer
Join
( Select OBJECT_NAME(PS.object_id) As TableName
,SUM(PS.row_count) As RowCnt
From sys.dm_db_partition_stats As PS
INNER
JOIN
sys.indexes As I
ON I.object_id
= PS.object_id
AND I.index_id = PS.index_id
Where I.type_desc IN
(‘CLUSTERED’,‘HEAP’)
AND I.object_id
> 100
AND
OBJECT_SCHEMA_NAME(PS.object_id)
<>
‘sys’
Group
By PS.object_id) As C
On
OBJECT_NAME(I.id)
= C.TableName
Where
Coalesce(C.RowCnt,0)
> 0
Group
By
OBJECT_NAME(I.id)
,C.RowCnt
Order
By TableName
–RowsModSinceStats Desc
Hi Tom,
If we are interested to view user tables only, then instead of using “I.object_id > 100” we can make a join to sys.objects on object_id column and check for Type = ‘U’.
Thanks
Pawan Tewari
Agreed. You could join to the INFORMATION_SCHEMA.TABLES view also. Lots of ways to get the job done.