Get index creation date from SQL server

Question

How can I find the create date of an index. I am using SQL2008 R2. I checked sys.indexes but it does not have a create date so I joined the query with sys.objects. The thing is that the object id for an index and the table containing that index is same.

I am using this query...

select i.name, i.object_id, o.create_date, o.object_id, o.name from sys.indexes i  join sys.objects o on i.object_id=o.object_id  where i.name = 'Index_Name' 

Thanks!

Answer 1

For indexes that are constraints, then see marc_s' answer

For other indexes, you'd have to use STATS_DATE to get the creation time of the assoicated index (every index has statistics on it)

Something like (not tested)

SELECT STATS_DATE(OBJECT_ID('MyTable'),  (SELECT index_id FROM sys.indexes WHERE name = 'Index_Name')) 

This relies on the sys.indexes to sys.stats links

Edit: there is no way to find out as far as anyone can find out. Sorry.

Answer 2

Simple query to list indexes in descending date (of statistics) order. This date is the sate of last statistics update, so is only reliable for recently created indexes.

select STATS_DATE(so.object_id, index_id) StatsDate , si.name IndexName , schema_name(so.schema_id) + N'.' + so.Name TableName , so.object_id, si.index_id from sys.indexes si inner join sys.tables so on so.object_id = si.object_id order by 1 desc