[statistics] Find last update time for all statistics for a table

(tbd 2015/09/09 - added truncate table to the loop.  Doh!)

Statistics matter. So you want to find when they were last updated.  You usually see this one:


SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('pad_tx')

Which works - for all statistics tied to an index.  But SQL Server has more than just those: there are also the _WA_Sys stats, which begin with that phrase and are created by the engine.  (IIRC, you can also see DTA stats, built by the Database Tuning Advisor) 

In theory you shouldn't have many of them, because they're only generated if you're querying a field that isn't part of an index.  Theoretically you should clean them out (that may be another post), since a stat could be in there twice - once for an index and once because you queried the table (creating the stat) before you created the index.

This should list all of them.  

DECLARE @sql NVARCHAR (1000), @MIN INT, @MAX INT, @statname NVARCHAR(128)

declare @listofstats TABLE (id int identity, NAME sysname)

INSERT INTO @listofstats (name) 

SELECT name FROM sys.stats WHERE object_id = object_id('yourtablenamehere')

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), 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 FROM @listofstats WHERE id = @min

SELECT @sql = N'DBCC SHOW_STATISTICS (''yourdatabasenamehere..yourtablenamehere'','''+ @statname +''')'

INSERT INTO #stats_info 

EXEC master..sp_executesql @sql

INSERT INTO #stats_info2 SELECT @statname, * FROM #stats_info

SET @min = @min + 1

end

DBCC TRACEOFF (2388)

SELECT #stats_info2.stat_name, #stats_info2.updated FROM #stats_info2 INNER JOIN (SELECT stat_name, MAX(updated) AS max_date FROM #stats_info2 GROUP BY stat_name)a 

ON #stats_info2.stat_name = a.stat_name AND #stats_info2.[updated] = a.max_date

ORDER BY #stats_info2.stat_name