SQL Server Performance

When was index created?

Discussion in 'General Developer Questions' started by cejar, Dec 2, 2003.

  1. cejar New Member

    Does any one know of a way to find out when the indexes where created? I don't think sp_help can can help with this. A script that can report a list of tables names with indexes and when the indexes where created would really help me out.

    Cheers!
    cejar, Dec 2, 2003
    #2

  2. Luis Martin Moderator

    Difficult, I think. Even is that date exist, when DBCC RBINDEX run, means drop and create that index again.
    Wait for others oppinions.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
    Luis Martin, Dec 2, 2003
    #2

  3. gaurav_bindlish New Member

    how about sysindexes and sysobjects table? Does it have a field for the same?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
    gaurav_bindlish, Dec 2, 2003
    #2

  4. Luis Martin Moderator

    Sysobjects has creation date, but sysindexes not.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
    Luis Martin, Dec 2, 2003
    #2

  5. satya Moderator

    Run SP_HELP indexname which gives you the information.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 2, 2003
    #2

  6. Jon M Member

    Satya,
    I'm just curious, have you tried SP_HELP indexname?

    Because per BOL, the @objname argument is the name of any object, in sysobjects or any user-defined data type in the systypes table.

    I believe index names are in sysindexes table.

    I tried running:
    SP_HELP IX_test --which is my index name
    but it did not work.

    Jon M
    Jon M, Dec 4, 2003
    #2

  7. satya Moderator

    Jon

    Yes, on my machine when I run the above query it results correctly without any issue.
    (I don't have SQL installed on my home laptop, so took this long to answer)

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 8, 2003
    #2

  8. Twan New Member

    This only works if the index happens to also be a constraint (unique, primary or foreign...)

    I don't think that there is a way to get this information for all indexes...

    Cheers
    Twan
    Twan, Dec 8, 2003
    #2

  9. satya Moderator

    Well spotted by Twan, I must have checked that one before though.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 8, 2003
    #2

  10. satya Moderator

    There was an article in SQL Server magazine by Kalen Delaney and I think it may be recent about index creation & there were some tests for creation times - you may be able to extrapolate from that article.

    HTH

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 15, 2003
    #2

Share This Page