List the last 4 times that each statistic in a database was updated

last_4_stats_updates_for_a_database

--Written by mbourgon 2016 as per http://thebakingdba.blogspot.com/2016/08/statistics-get-last-4-stat-update-time.html
--CC, noncommerical with attribution.  Use it as you want noncommercially, but this notice must remain.

--run in the database you need to evaluate.
DECLARE @sql NVARCHAR (1000), @MIN INT, @MAX INT, @stats_name NVARCHAR(128), @tablename sysname, @the_schema_name sysname, @db_name sysname
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,12), [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 (the_schema_name sysname, table_name sysname, stat_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))

declare @listofstats TABLE (id int identity, the_schema_name sysname, table_name sysname, stats_name sysname)
INSERT INTO @listofstats
SELECT OBJECT_SCHEMA_NAME(object_id) AS the_schema_name, 
		OBJECT_NAME(object_id) AS table_name, 
		name AS stats_name 
FROM sys.stats 
WHERE auto_created = 1		--1 is automatically created (aka _WA_Sys_00000003_53D770D6), 0 is index-based.
		AND OBJECT_ID >100	--this should exclude any system processes

SELECT @MIN = MIN(ID), @max = MAX(id) FROM @LISTOFSTATS 

DBCC TRACEON (2388)	--needed to get the last 4 rebuild dates, not just the most current, as per Nayan Raval.
WHILE @min <= @max
begin
 TRUNCATE TABLE #stats_info 
 SELECT @sql = NULL, @stats_name = NULL, @the_schema_name = NULL, @tablename = NULL 
 SELECT @the_schema_name = QUOTENAME(the_schema_name), 
		@tablename = QUOTENAME(table_name), 
		@stats_name = stats_name,
		@db_name = DB_NAME(DB_ID())
 FROM @listofstats WHERE id = @min

 SELECT @sql = N'DBCC SHOW_STATISTICS ("' + @db_name + '.' + @the_schema_name+ '.' + @tablename+ '",'''+ @stats_name +''')' 
 --if not including the dbname, you need 

PRINT @sql

 INSERT INTO #stats_info 
  EXEC master..sp_executesql @sql
 INSERT INTO #stats_info2 SELECT @the_schema_name, @tablename, @stats_name, * FROM #stats_info
 
 SET @min = @min + 1
end

DBCC TRACEOFF (2388)

SELECT * FROM #stats_info2 ORDER BY the_schema_name, table_name, stat_name