This post is basically to answer a question asked in class this week: How can we get the last statistics update date for ALL user tables in a database?
After working on the query for a while, I realized that the new metadata function I posted about here can give you that info easily:
SELECT object_name(sp.object_id) as object_name,name as stats_name, sp.stats_id,
last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE sp.object_id > 100;
But for those of you not yet running SQL Server 2008R2 Service Pack 2 or SQL Server 2012 Service Pack 1 (as long as it’s 2005 or later), I’ll show you the other query I had worked out:
SELECT schema_name(schema_id) AS SchemaName, object_name(o.object_id) AS ObjectName,
i.name AS IndexName, index_id, o.type,
STATS_DATE(o.object_id, index_id) AS statistics_update_date
FROM sys.indexes i join sys.objects o
on i.object_id = o.object_id
WHERE o.object_id > 100 AND index_id > 0
AND is_ms_shipped = 0;
There are a few slight differences in the output between the two queries, in addition to the fact that the query using sys.dm_db_stats_properties() adds the extra columns for number of rows sampled, the column modification counter, etc. The function does does not return the schema_id or the type of object (e.g. is it a table or an indexed view). Both those pieces of information can be determined by joining with the sys.objects view, but I have left that as an exercise for the reader/student.
I hope you find this useful!