Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers 2

up vote 5 down vote accepted

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
share|improve this answer
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 '11 at 1:39
    
Thank you very much –  Manjot May 13 '11 at 1:41
2  
If it isn't too much trouble, please could you summarise the contents of the link in the body of your answer? Thanks :) –  Jack Douglas Dec 24 '11 at 17:59
    
Wow, ... usually I'm not that lazy. No problem - answer has been updated. –  RThomas Dec 24 '11 at 20:19
add comment

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

share|improve this answer
2  
Answer not wrong but @Alejandro, does it provide anything that the other - 2 years old - answer does not? –  ypercube Sep 9 '13 at 23:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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