/* 
Author: Dev Nambi
Website: www.devnambi.com 
Version: 1.0
Please see the website for copyright details (Creative Commons)
*/

set nocount on;
declare @CurrentRowModCounter bigint
	,@RowModCounterThreshold bigint
	,@RowModPercentageThreshold decimal(3,2)
	,@StatsSamplingPercentage tinyint
	,@Msg nvarchar(max)
	,@Cmd nvarchar(max)
	,@QualifiedTableName nvarchar(256)
	,@CurrentTableRowcount bigint

declare @TableList table
(SchemaName sysname not null
,TableName sysname not null
,RowModCounter bigint not null
,EstimatedRowcount bigint not null
,primary key clustered (SchemaName,TableName))

/* 
User options:
@RowModCounterThreshold -- if a table gets more than this many rows inserted/updated/deleted, update its statistics
@RowModPercentageThreshold -- if a table gets more than this percentage of rows inserted/updated/deleted, update its statistics
@StatsSamplingPercentage:
	--set to 0 to update stats in RESAMPLE mode. 
	--set to null to update statistics in their default configuration
	--set to a value between 1 and 100 to update statistics by scanning that percentage of the table's rows
*/
set @RowModCounterThreshold=50000 --change if 50K rows have changed
set @RowModPercentageThreshold=0.05 --change to a 5% threshold
set @StatsSamplingPercentage=0 --use resample mode

insert into @TableList
(SchemaName, TableName, RowModCounter, EstimatedRowcount)
select
	s.name as SchemaName
	,t.name as TableName
	,max(si.rowmodctr) as RowModCounter
	,SUM(p.rows) as EstimatedRowCount
from sysindexes si
inner join sys.tables t
on si.id=t.object_id
inner join sys.schemas s
on t.schema_id=s.schema_id
inner join sys.partitions p
on p.object_id=t.object_id
where si.indid in (0,1)
and p.index_id in (0,1)
group by s.name, t.name


declare Csr cursor local read_only for 
select
N'['+SchemaName+'].['+ TableName+N']'
,RowModCounter
,EstimatedRowcount
from @TableList
where EstimatedRowcount > 0

open Csr

fetch next from Csr into @QualifiedTableName, @CurrentRowModCounter, @CurrentTableRowcount
begin
	
	select
		@QualifiedTableName as TableName
		,@CurrentRowModCounter as RowModCounter
		,@RowModCounterThreshold as RowModCounterThreshold
		,@CurrentRowModCounter*1.0/@CurrentTableRowcount as PercentageOfTableChanged
		,@RowModPercentageThreshold as PercentageOfTableChangedThreshold
		,@CurrentTableRowcount as TableRowcount
	
	
	set @msg='--Description: '+
		case 
			when @CurrentRowModCounter > @RowModCounterThreshold
				and @RowModPercentageThreshold < @CurrentRowModCounter*1.0/@CurrentTableRowcount
				then 'update stats on '+@QualifiedTableName
					+' because row mod counter ('+CONVERT(nvarchar,@CurrentRowModCounter)+N')'
					+N' exceeded threshold ('+CONVERT(nvarchar,@RowModCounterThreshold)+N')'
					+N' and percentage changed ('+CONVERT(nvarchar,@CurrentRowModCounter*1.0/@CurrentTableRowcount)+N'%)'
					+N' exceeded threshold ('+CONVERT(nvarchar,@RowModPercentageThreshold)+N'%)'
			when @CurrentRowModCounter > @RowModCounterThreshold
				then 'update stats on '+@QualifiedTableName
					+' because row mod counter ('+CONVERT(nvarchar,@CurrentRowModCounter)+N')'
					+N' exceeded threshold ('+CONVERT(nvarchar,@RowModCounterThreshold)+N')'
			when @RowModPercentageThreshold  < @CurrentRowModCounter*1.0/@CurrentTableRowcount
				then 'update stats on '+@QualifiedTableName
					+' because percentage changed ('+CONVERT(nvarchar,@CurrentRowModCounter*1.0/@CurrentTableRowcount)+N'%)'
					+N' exceeded threshold ('+CONVERT(nvarchar,@RowModPercentageThreshold)+N'%)'
			else 'do not update stats on '+@QualifiedTableName
				+' because row mod counter ('+CONVERT(nvarchar,@CurrentRowModCounter)+N')'
					+N' did not exceed threshold ('+CONVERT(nvarchar,@RowModCounterThreshold)+N')'
					+N' and percentage changed ('+CONVERT(nvarchar,@CurrentRowModCounter*1.0/@CurrentTableRowcount)+N'%)'
					+N' did not exceed threshold ('+CONVERT(nvarchar,@RowModPercentageThreshold)+N'%)'
		end
	print @msg

	if @CurrentRowModCounter > @RowModCounterThreshold
	or @RowModPercentageThreshold  < @CurrentRowModCounter*1.0/@CurrentTableRowcount
	begin
		set @cmd='update statistics '+@QualifiedTableName
			+case 
				when @StatsSamplingPercentage=0 
					then ' with resample'
				when @StatsSamplingPercentage is not null
					then ' with sample '+convert(nvarchar,@StatsSamplingPercentage)+' percent'
				else N'' end
			
			print (@cmd)
	end
	fetch next from Csr into @QualifiedTableName, @CurrentRowModCounter, @CurrentTableRowcount
end

close Csr
deallocate Csr