When was my index created or at least last updated?

1

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.

indexdates1

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')

indexdates2

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

indexdates3

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.

One thought on “When was my index created or at least last updated?

  1. […] Step one is to go to connect.microsoft.com and search for your suggestion/bug to see if it already exists. I’m going to ask for a create date and a modified date for indexes. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,655 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: