SQL Server – Get Index Creation Date

There are many T-SQL scripts to get the index creation date but I always use this one.

 DECLARE @filename VARCHAR(500) 
SELECT @filename = CAST(value AS VARCHAR(500)) 
FROM fn_trace_getinfo(DEFAULT) 
WHERE property = 2 
  AND value IS NOT NULL 

-- Go back 4 files since default trace only keeps the last 5 and start from there.
SELECT @filename = substring(@filename, 0, charindex('_', @filename)+1) + convert(varchar, (convert(int, substring(left(@filename, len(@filename)-4), charindex('_', @filename)+1, len(@filename)))-4)) + '.trc'

SELECT 
       gt.EventClass, 
       gt.EventSubClass,
       te.Name AS EventName,
       gt.HostName, 
       gt.StartTime, 
       gt.DatabaseName,
       gt.ObjectName
FROM fn_trace_gettable(@fileName, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass = 46
  and ObjectType = 22601
  and gt.DatabaseName <> 'tempdb'
ORDER BY StartTime desc; 

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter