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.

Filed under: Index, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: index, microsoft sql server, T-SQL