[Statistics] Let's find the last 4 update times for each statistic in a database.

Needed to stretch my prior post's code.

	--gets the last 4 stats updates for each non-system/non-MS table/statistic
	--thebakingdba.blogspot.com
	use yourdatabasenamehere
	DECLARE @sql NVARCHAR(1000),
	@MIN INT,
	@MAX INT,
	@statname NVARCHAR(128),
	@tablename NVARCHAR(128),
	@schemaname NVARCHAR(128);
	DECLARE @listofstats TABLE
	(
	id INT IDENTITY,
	NAME sysname,
	objectid BIGINT
	);
	INSERT INTO @listofstats
	(
	NAME,
	objectid
	)
	SELECT s.name,
	s.object_id
	FROM sys.stats s
	INNER JOIN sys.objects o
	ON o.object_id = s.object_id
	WHERE o.object_id >= 100
	AND o.type_desc NOT IN ('internal_table', 'system_table',
	'TYPE_TABLE')
	AND OBJECT_NAME(s.object_id) NOT LIKE 'MS%';
	IF OBJECT_ID('tempdb..#stats_info') IS NOT NULL
	DROP TABLE #stats_info;
	CREATE TABLE #stats_info
	(
	updated DATETIME,
	[Table cardinality] BIGINT,
	[snapshot ctr] BIGINT,
	steps INT,
	density DECIMAL(19, 16),
	[rows above] INT,
	[rows below] INT,
	[squared variance error] DECIMAL(19, 16),
	[inserts since last update] MONEY,
	[deletes since last update] MONEY,
	[leading column type] VARCHAR(50)
	);
	IF OBJECT_ID('tempdb..#stats_info2') IS NOT NULL
	DROP TABLE #stats_info2;
	CREATE TABLE #stats_info2
	(
	stat_name VARCHAR(128),
	schema_name VARCHAR(128),
	table_name VARCHAR(128),
	updated DATETIME,
	[Table cardinality] BIGINT,
	[snapshot ctr] BIGINT,
	steps INT,
	density DECIMAL(19, 16),
	[rows above] INT,
	[rows below] INT,
	[squared variance error] DECIMAL(19, 16),
	[inserts since last update] MONEY,
	[deletes since last update] MONEY,
	[leading column type] VARCHAR(50)
	);
	SELECT @MIN = MIN(id),
	@MAX = MAX(id)
	FROM @listofstats;
	DBCC TRACEON (2388);
	WHILE @MIN <= @MAX
	BEGIN
	TRUNCATE TABLE #stats_info;
	SELECT @sql = NULL,
	@statname = NULL;
	SELECT @statname = NAME,
	@tablename = OBJECT_NAME(objectid),
	@schemaname = OBJECT_SCHEMA_NAME(objectid)
	FROM @listofstats
	WHERE id = @MIN;
	SELECT @sql = N'DBCC SHOW_STATISTICS (''erxpad.' + @schemaname
	+ '.' + @tablename + ''',''' + @statname + ''')'
	FROM @listofstats
	WHERE id = @MIN;
	INSERT INTO #stats_info
	EXEC master..sp_executesql @sql;
	INSERT INTO #stats_info2
	SELECT @statname,
	@schemaname,
	@tablename,
	*
	FROM #stats_info;
	SET @MIN = @MIN + 1;
	END;
	DBCC TRACEOFF (2388);
	SELECT *
	FROM #stats_info2
	ORDER BY schema_name,
	table_name,
	stat_name,
	updated;

view raw find_recent_stats_for_a_database.sql hosted with ❤ by GitHub