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

Using Tsql, how can i know when Index rebuild, reorg or updatestatistics was last run on SQL开发者_如何学Python server (2000, 2005 or 2008)?

Regards

SQL Server does not store this information. You can get an "approximate" estimate based on the date the last time the statistics were updated for a given Index as a REBUILD operation will 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

You may want to consider using one of the many freely available Index Tuning maintenance scripts for SQL Server. These provide a means to store the details of the Index operations that are performed on a given database and thereby provide information as to when an Index was last rebuilt.

Take a look at the excellent Index Maintenance Script written by Michelle Ufford

For SQL Server 2000, the following script may be needed as sys.stats does not exist in SQL 2000:

DBCC SHOW_STATISTICS(TABLENAME, INDEX_NAME)

Many thanks for your script John!

EDIT: I've been contemplating this script for SQL 2005+ (and have had a need to see the data in TableName, IndexName, LastStatsUpdate format), and I came up with the following script.
I'm sure it needs polishing, so feel free to tweak as necessary.

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