SQL SERVER DBA, Linux and Azure: T-SQL scripts to find out when last stats update happened ?

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

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

2 comments:

  1. I am impressed by the details that you have shared in this post and It reveals how nicely you understand this subject. If you want to get Database Replication Tools Onlinethen, Repstance.com is here to guide you.

    ReplyDelete