In SQL Server 2005 and above, how can I find when an index was created?
2 Answers
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
-
1I 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.– RThomasMay 13, 2011 at 1:39
-
3If 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.– RThomasDec 24, 2011 at 20:19
There is no way to know a single creation date of an index because it is not saved or collected by SQL Server.
-
3Answer not wrong but @Alejandro, does it provide anything that the other - 2 years old - answer does not? Sep 9, 2013 at 23:31