Tsql, you know, when the rebuild, reorg or updatestatistics index was last run on the SQL server

Question

Using Tsql, how do you know when the index, reorg, or updatestatistics on the SQL server was last updated (2000, 2005 or 2008)?

Hi

Answer 1

SQL Server does not save this information. You can get a rough estimate based on the date the statistics were last updated for this index as a REBUILD operation, also update the statistics for the index.

Here is an example using the AdventureWorks database:

USE AdventureWorks;
GO

SELECT name AS Stats,
STATS_DATE(object_id, stats_id) AS LastStatsUpdate
FROM sys.stats
WHERE object_id = OBJECT_ID('Sales.SalesOrderDetail')
and left(name,4)!='_WA_';

GO

, SQL Server. , , , .

Index Maintenance Script,

Answer 2

SQL Server 2000 script, sys.stats SQL 2000:

DBCC SHOW_STATISTICS(TABLENAME, INDEX_NAME)

script John!

EDIT: script SQL 2005+ ( TableName, IndexName, LastStatsUpdate), script.
, , .

SELECT OBJECT_NAME(object_id) [TableName], 
       name [IndexName],
       STATS_DATE(object_id, stats_id) [LastStatsUpdate]
FROM sys.stats
WHERE name NOT LIKE '_WA%'
AND STATS_DATE(object_id, stats_id) IS NOT NULL
AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
ORDER BY TableName, IndexName