To Find Index Creation Date

-- To find Index Creation Date


SELECT schema_name(o.schema_id) AS SchemaName

      , object_name(si.object_id) AS TableName

      , si.name AS IndexName

      , STATS_DATE(i.object_id, i.index_id) AS StatDate

FROM sys.indexes si

JOIN sys.objects o ON si.object_id = o.object_id

JOIN sys.indexes i ON i.object_id = si.object_id AND i.index_id = si.index_id

WHERE o.type <> 'S' -- exclude system objects

  AND STATS_DATE(i.object_id, i.index_id) IS NOT NULL;

===============================================================
  OR

select
    i.name as IndexName,
    o.name as TableName,
    ic.key_ordinal as ColumnOrder,
    ic.is_included_column as IsIncluded,
    co.[name] as ColumnName,
    o.create_date
from sys.indexes i
join sys.objects o on i.object_id = o.object_id
join sys.index_columns ic on ic.object_id = i.object_id
    and ic.index_id = i.index_id
join sys.columns co on co.object_id = i.object_id
    and co.column_id = ic.column_id
where i.[type] = 2
--and i.is_unique = 0
--and i.is_primary_key = 0
--and o.[type] = 'U'
--and ic.is_included_column = 0
order by o.[name], i.[name], ic.is_included_column, ic.key_ordinal;

=====================================================================

OR

--sort as you wish, you have the most recently created index first

select
crdate,
i.name,
object_name(o.id)
-- , * --optionally
from
sysindexes i
join sysobjects o ON o.id = i.id
order by
crdate desc