T-SQL scripts to find out when last stats update happened ?

select a.id as 'ObjectID', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName',
stats_date (id,indid) as stats_last_updated_time,st.auto_created
from sys.sysindexes as a
inner join sys.objects as b
inner join sys.stats st on st.object_id=b.object_id
on a.id = b.object_id
where b.type = 'U'

---------------------------------

SELECT
t.name AS Table_Name
,i.name AS Index_Name
,i.type_desc AS Index_Type
,STATS_DATE(i.object_id,i.index_id) AS Date_Updated
,sp.rows --if you want to know how many rows unrem this
FROM
sys.indexes i JOIN
sys.tables t ON t.object_id = i.object_id JOIN
sys.partitions sp ON i.object_id = sp.object_id
and i.index_id = sp.index_id --new

WHERE
i.type > 0 and --if you want to see heap rem this
sp.rows > 0
ORDER BY
t.name ASC
,i.type_desc ASC
,i.name ASC

------------------------

1 comment: