Search This Blog

Friday, November 9, 2012

Find Last Index Rebuild Time for all Databases on SQL Server


IF EXISTS(SELECT 'X' FROM tempdb..sysobjects where name like '#TABLE_VER%')
drop table #TABLE_VER
CREATE TABLE #TABLE_VER (DBNAME VARCHAR(50), INDEX_DATE DATETIME)

DECLARE @command varchar(1000)

SELECT @command =
'USE [?] INSERT INTO #TABLE_VER SELECT db_name() ,
(SELECT max(STATS_DATE(object_id, index_id)) FROM sys.indexes)'

EXEC sp_MSforeachdb @command

SELECT ' Index Rebuild Date'
UNION
SELECT '**************************************************************'
union
SELECT 'Index Rebuild Ran on '+ DBNAME + ' ON ' + CONVERT(VARCHAR(12),INDEX_DATE) FROM #TABLE_VER WHERE DBNAME NOT IN ('master','tempdb','model','msdb')

1 comment:

pl80 said...

But this shows the date of the last statistics update, which can be triggered by many things, not just an index rebuild.