This blog is for SQL Developers who would like to tranform themselves to DBA
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')
Subscribe to:
Post Comments (Atom)
1 comment:
But this shows the date of the last statistics update, which can be triggered by many things, not just an index rebuild.
Post a Comment