9

In SQL Server 2005 and above, how can I find when an index was created?

2 Answers 2

12

My answer is a direct quote of this link:

http://www.sqlservercentral.com/Forums/Topic960549-391-1.aspx#bm960643

There is no direct way of finding the creation date of an index. But however if you look in sysobjects there is a creation date for Primary Key or Unique Constraints.Indexes associated with primary Primary Key or Unique Constraints creation date can be known.

check if there are any new DMVs (Dynamic Mgmt Views) in sql server 2008 which can help you in getting creationDate.

try this query

select crdate, i.name, object_name(o.id)
from sysindexes i
   join sysobjects o ON o.id = i.id
order by crdate desc
3
  • 1
    I originally posted sys.objects but then I thought about it more and realized that wouldn't work. Indexes aren't in there only constraints created by indexes. Found this link that might help explain.
    – RThomas
    May 13, 2011 at 1:39
  • 3
    If it isn't too much trouble, please could you summarise the contents of the link in the body of your answer? Thanks :) Dec 24, 2011 at 17:59
  • Wow, ... usually I'm not that lazy. No problem - answer has been updated.
    – RThomas
    Dec 24, 2011 at 20:19
-2

There is no way to know a single creation date of an index because it is not saved or collected by SQL Server.

1
  • 3
    Answer not wrong but @Alejandro, does it provide anything that the other - 2 years old - answer does not? Sep 9, 2013 at 23:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.