Index Created Date

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  July 9, 2017 at 8:33 pm

  #322471

  How to find Index created date details in my SQL server database?.Any query to find for this.In my database lot of indexes are there.I want to know   index created date with name details.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 9, 2017 at 8:56 pm

  #1950116

  With the exceptions of indexes created as part of a primary key or unique constraint, then no. SQL does not save the creation/modify date of indexes. you can get the create_date and modify_date from sysobjects for the PK/UQs only.
  That date doesn't really have much value,  if it was exposed.
  going forward, if you needed to know who was creating indexes behind your back(Bobby Bouche!)
  then you could add an extended event that captures DDL statements, or a DDL trigger.
  you can kinda-sorta find a date via the STATS_DATE function, that gives you the last time an index was rebuilt/reorganized, or a statistic was last updated. that's only useful if you never, ever urebuild stats or indexes, though. it might imply a newly created indexes date though.

  SELECT STATS_DATE([so].[object_id], [si].[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;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!