Script to get the last update statistics date


Below script will execute against the sys.Objects,sys.indexes and sys.stats. Script will return last statistics update date and meta data of  statistics. Script will return the result for table and view level statistics.

In this script I have used the function STATS_DATE which will return last updated statistics date.

Syntax
STATS_DATE ( table_id , index_id )

SELECT sysobj.name AS objectname,
sysindex.name AS indexname,
Stats_date(sysindex.[object_id], sysindex.index_id) AS
[Statistics Update Date],
CASE sysstats.auto_created
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedbyqueryprocessor,
CASE sysstats.user_created
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedbyuser,
CASE sysstats.no_recompute
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedwithnorecomputeoption
FROM sys.objects AS sysobj WITH (nolock)
INNER JOIN sys.indexes AS sysindex WITH (nolock)
ON sysobj.[object_id] = sysindex.[object_id]
INNER JOIN sys.stats AS sysstats WITH (nolock)
ON sysindex.[object_id] = sysstats.[object_id]
AND sysindex.index_id = sysstats.stats_id
WHERE sysobj.[type] IN ( ‘U’, ‘V’ )
ORDER BY Stats_date(sysindex.[object_id], sysindex.index_id);

Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s