Thursday, May 26, 2011

Index Creation and Modification Date.

Ever wondered when that index was created !

Select  s.name, t.name, t.create_date, t.modify_date,i.name, c.name
From sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
        inner join sys.columns c on c.object_id = t.object_id and
                ic.column_id = c.column_id
Where i.index_id > 0   
and i.type in (1, 2) -- clustered & nonclustered only
and i.is_primary_key = 0 -- do not include PK indexes
and i.is_unique_constraint = 0 -- do not include UQ
and i.is_disabled = 0
and i.is_hypothetical = 0
and ic.key_ordinal > 0

Order by ic.key_ordinal


well I'm not sure what's the best way, or if there's any way to get this info.
but you could get the table's creation date and use that as a reference....please note that not in every case you build your index the same date as your table!

also as one poster in a forum advised to use the statistics creation date!

http://stackoverflow.com/questions/7579932/get-index-creation-date-from-sql-server