Update Statistics based on the Percentage of Modifications

As the sp_updatestats nearly update all statistics in your database (it will update a statistic as long as it has had one row modified), I don't recommend this old fashioned method. A new DMF called dm_db_stats_properties was added in SQL Server 2008 R2 SP2 and SQL Server 2012 SP1, we can use this DMF to check the number of modifications for the leading statistics column (the column on which the histogram is built) since the last time statistics were updated. Based on the percentage of modifications (#modifications / #total rows), we can pick out the statistics to update.

DECLARE @modifiedPercent int = 20

SELECT
'UPDATE STATISTICS ' + sch.name + '.' + so.name + ' [' + ss.name + '] WITH FULLSCAN' AS UpdateStatsStmt,
sch.name + '.' + so.name AS TableName,
ss.name AS Statistic,
sp.last_updated AS StatsLastUpdated,
sp.rows AS FilteredRows,
sp.unfiltered_rows AS TotalRows,
sp.rows_sampled AS RowsSampled,
sp.modification_counter AS Modifications
FROM sys.stats AS ss
JOIN sys.objects AS so ON ss.object_id = so.object_id
JOIN sys.schemas AS sch ON so.schema_id = sch.schema_id
CROSS APPLY sys.dm_db_stats_properties (so.object_id, ss.stats_id) AS sp
WHERE so.type = 'U'
AND sp.modification_counter / sp.unfiltered_rows >= @modifiedPercent
ORDER BY TableName