Wednesday, January 6, 2016

Can I get the index creation date from SQL Server?

um.... No.  Well, not for ALL of them, anyway.  The sys.indexes catalog view does not include a creation date for the indexes, and sys.objects only stores data for indexes associated with primary key and unique constraints.  So... we do not have a create date for indexes that are not associated with primary or unique constraints.

Take a look at the two queries below;  the 1st one joins sys.objects and sys.indexes, but the date returned is of the table creation, not the index. It may be close, but it's not guaranteed to be the date the index was created.  The 2nd query, however, just takes a look into sys.objects for the create date (crdate) of the index that SQL Server creates for us automatically when any PRIMARY KEY or UNIQUE constraint is created. This one is definitely IS the create date for the indexes associated with each constraint.

-- #1. Not the index create date
USE AdventureWorks2012;
SELECT
    i.name [IndexName],
    o.name [TableName],
    o.create_date [Created] -- this is the table, not the index
FROM
    sys.indexes i INNER JOIN sys.objects o
      ON i.object_id = o.object_id
WHERE
    o.name IN ('BusinessEntityContact','UnitMeasure')

Results:



     






-- #2. crdate for primary/unique constraint indexes
SELECT
    name [IndexName],
    crdate [Created]
FROM
    sys.sysobjects
WHERE
    xtype IN('pk','uq'-- all you need to find pk/uq constraints
    AND name IN
('PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID','PK_UnitMeasure_UnitMeasureCode')

Result:
    







The AND portion of the WHERE clause in query #2 is only there because I wanted to give you back the same constraints returned in query #1.  If you take a look at the create dates for both, you will see they are definitely different than the date the tables were created.

So... can we get the created date for ALL indexes?  Sure, but not with out-of-the-box SQL Server. You could use a DDL event trigger to audit object creation/modifications. Possibly the topic of a later post....

Until then, please take a look at this reference to sys.indexes.  

    https://msdn.microsoft.com/en-IN/library/ms173760.aspx

No comments:

Post a Comment