Statistics in SQL Server - Microsoft SQL Server 2012 Performance Tuning - page 291

Getting ready
In order to get the desired information, the following system view and compatibility view
will be used.
Sys.SysIndexes: This compatibility view provides RowModCtr column, which is the heart of
the script.
Sys.Indexes: Though we can get information about RowModCtr from the Sys.SysIndexes
view, we have to join it with Sys.Indexes , using the table's ID, in order to get statistics name
as well as the last update date.
Sys.Objects: The schema name will be received from the Sys.Objects view.
How to do it...
The following script will show you all statistics that have a greater RowModCtr value than
zero, in descending order along with the UPDATE STATISTICS command:
SELECT DISTINCT
OBJECT_NAME(SI.object_id) as Table_Name
,SI.[name] AS Statistics_Name
,STATS_DATE(SI.object_id, SI.index_id) AS Last_Stat_Update_Date
,SSI.rowmodctr AS RowModCTR
,SP.rows AS Total_Rows_In_Table
,'UPDATE STATISTICS ['+SCHEMA_NAME(SO.schema_id)+'].['
+ object_name(SI.object_id) + ']'
+ SPACE(2) + SI.[name] AS Update_Stats_Script
FROM
sys.indexes AS SI (nolock) JOIN sys.objects AS SO (nolock)
ON
SI.object_id=SO.object_id
JOIN
sys.sysindexes SSI (nolock)
ON
SI.object_id=SSI.id
AND
SI.index_id=SSI.indid
JOIN
sys.partitions AS SP
ON
SI.object_id=SP.object_id