Statistics - find last 4 stats for a database

find_recent_stats_for_a_database.sql

--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;