November 14, 2016 by Kenneth Fisher
SQL Server stores a create date and a change date for each object in the sys.objects system view.
Unfortunately while tables, views and even constraints are objects, indexes are not. Or at least they aren’t stored in the sys.objects system view. And the sys.indexes system view doesn’t have any dates associated with it. So how do we get the create/update date on an index? Well, short answer is you don’t. Long answer is that in some cases you can get some information.
Unique indexes are used to enforce unique constraints and primary keys. So for these indexes we can tie back to the associated object in sys.objects. Unfortunately we have to use the name as part of the key which I’m not overly fond of but hey, that’s the way it is.
SELECT object_schema_name(objects.parent_object_id) AS Object_Schema_Name, object_name(objects.parent_object_id) AS Object_Name, sys.indexes.name AS Index_Name, sys.objects.create_date, sys.objects.modify_date FROM sys.objects JOIN sys.indexes ON objects.parent_object_id = indexes.object_id AND objects.name = indexes.name -- Check for Unique Constraint & Primary key AND objects.type IN ('UQ','PK')
Now since most indexes aren’t associated with unique constraints that’s going to be of limited help. There is one other place we can look. Equally limited though, I’m afraid. Every index has statistics associated with it. So we can look at the sys.stats. Unfortunately sys.stats doesn’t have a date either. So we add in STATS_DATE.
SELECT object_schema_name(stats.object_id) AS Object_Schema_Name, object_name(stats.object_id) AS Object_Name, indexes.name AS Index_Name, STATS_DATE(stats.object_id, stats.stats_id) AS Stats_Last_Update FROM sys.stats JOIN sys.indexes ON stats.object_id = indexes.object_id AND stats.name = indexes.name
STATS_DATE returns the last datetime the statistic was updated. This isn’t exactly what we were looking for but it’s better than nothing. Which kind of sums up this whole post. Better than nothing.