CODEWITHSUNDEEP
sql-servert-sql
Shane Kenyon
Shane Kenyon

Reputation: 5381

TSQL Query Indexes by date to determine when created?

Is there a way in SQL Server 2008 R2 to determine when an index was created via TSQL? The sys.indexes table does not have any date fields on it. Thanks.

Upvotes: 3

Views: 8458

Answers (1)

Kevin Dahl
Kevin Dahl

Reputation: 772

You can join it to sys.objects, which has a create_date field.

SELECT TOP 10
        *
FROM    <yourdb>.sys.indexes i
        INNER JOIN <yourdb>.sys.objects o ON i.object_id = o.object_id
WHERE o.type NOT IN ('S', 'IT')
ORDER BY create_date DESC

Re-edit-the-edit: I shouldn't attempt to communicate before I've had my morning coffee.

The same caveats to that answer linked apply, as they're not actually the creation date of indexes, but related objects.

If that is something that you need to be able to do, you could log the DDL statements:

http://madhuottapalam.blogspot.ca/2008/06/faq-how-to-find-index-creation-rebuild.html

Upvotes: 1

Related Questions