Tag: Update Statistics Date

  • 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);