Post reply

how to check the index creation or modified datetime?

  • October 17, 2011 at 5:22 am

    #250092

    hi All,

    how to check the index (expect primary key and unique key) creation or modified datetime ?

    Thanks & Regrads

    Deepak.A

  • October 17, 2011 at 6:01 am

    #1395251

    PKs and UQ's constraints can be found in sys.objects;

    so the common fields ofr all objects like create_date and modify_date will get you what you wan:, I think:

    /*--Results

    TableNamecreate_datemodify_datename

    Tally2009-02-27 14:26:36.5772009-02-27 14:26:36.577PK_Tally_N

    Numbers2009-02-27 14:26:41.7172009-02-27 14:26:41.717PK_Numbers_Numbner

    WHORESULTS2011-09-28 16:23:16.5472011-09-28 16:23:16.547PK__WHORESULTS__54E305AD

    WHO_DATA2011-09-28 16:25:36.2002011-09-28 16:25:36.200PK__WHO_DATA__58B39691

    Empmaster2010-05-17 08:50:56.2702010-05-17 08:50:56.270PK_Empmaster

    DeptMaster2010-05-17 08:50:56.3502010-05-17 08:50:56.350PK_DeptMaster*/

    SELECT OBJECT_NAME(parent_object_id) AS TableName,

    create_date,

    modify_date,

    *

    FROM sys.objects

    WHERE type IN( 'PK', 'UQ' )

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 17, 2011 at 6:07 am

    #1395255

    Hi Lowell ,

    how to check the index creation for non -PK or Unique Key , example non-clustered index creation index date or filtered index or XML index ....

    Thanks & regards

    Deepak.A

  • October 17, 2011 at 6:16 am

    #1395260

    Ah, how 1 little word changes everything!

    expect except!

    And no I have no idea where to get that info.

    It's not in sys.indexes, sys.stats, indexproperty nor sys.dm_db_index_physical_stats dynamic management function and the likes.

    The only useful-ish idea I have is that you should see that in your change management system!

  • October 17, 2011 at 6:21 am

    #1395263

    i don't believe the index_creation_date / modify date exists(or at least is exposed); i posed a similar question ofn when is a server side trace Creation_date...it's not available eaither...jsut when it was last started.

    best i could find is the last time an index was used, and that's in select * from sys.dm_db_index_usage_stats

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 17, 2011 at 6:28 am

    #1395266

    Lowell (10/17/2011)

    i don't believe the index_creation_date / modify date exists(or at least is exposed); i posed a similar question ofn when is a server side trace Creation_date...it's not available eaither...jsut when it was last started.

    best i could find is the last time an index was used, and that's in select * from sys.dm_db_index_usage_stats

    I went down that road as well.

    Nothing like 1st time used. Stats date is the same problem. Won't be valid very long.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply