Post reply

When index was created

  • August 18, 2009 at 8:53 am

    #140971

    there is any statement I can run to find out when index was created

  • August 18, 2009 at 9:03 am

    #1040944

    select si.name, so.name, so.crdate from sys.sysindexes si inner join sys.sysobjects so on si.id = so.id

    where si.first is not null

    and

    si.name = 'your index name'

    and

    so.name = 'your table name'

    Providing you have permisions to query master!

    Oraculum

  • August 18, 2009 at 9:04 am

    #1040945

    SELECT i.name, o.create_date

    FROM sys.objects O INNER JOIN sys.indexes I

    ON o.object_id = i.object_id

    WHERE OBJECT_NAME(o.object_id) = 'TableName'

  • August 18, 2009 at 9:18 am

    #1040959

    unless i'm mistaken that gives you the table CREATE date, not he index create date

    sysobjects.id = the table id

    sysindexes.id = the table id

    sysobjects.name = table name

    sysindexes.name=index name

    sysindexes.indid= the index number of the index against that table (0= heap table, 1= table with clustered index, 2-254= all other indexes 255=text index)

    sysindexes.createdate = the table create date

    MVDBA

  • August 18, 2009 at 9:24 am

    #1040963

    Thanks, so there is any script we have to find out how many indexes need to be defragment

  • August 18, 2009 at 9:25 am

    #1040964

    So it is...Good spot 😀

  • August 18, 2009 at 9:28 am

    #1040968

    MAK (8/18/2009)

    Thanks, so there is any script we have to find out how many indexes need to be defragment

    Hi Mak,

    Michael pointed out that the date was actually the table create date - I can't find any create date for the index.

    For checking fragmentation, however, please refer to the sys.dm_db_index_physical_stats dmv. This will return all the information you need to evaluate the fragmentation on all indexes.

  • August 18, 2009 at 9:31 am

    #1040972

    do you know the parameters I try it but never figerout

  • August 24, 2015 at 1:45 pm

    #1822086

    I too need the INDEX CREATE DATE......not the Table Create Date. Any ideas of narrowing this down a bit farther????

    Thank you!!,

    Angelindiego

  • August 24, 2015 at 7:12 pm

    #1822122

    MAK-1128556 (8/18/2009)

    Thanks, so there is any script we have to find out how many indexes need to be defragment

    Yes. Lookup [font="Arial Black"]sys.dm_db_index_physical_stats [/font]and [font="Arial Black"]ALTER INDEX[/font] in Books Online to learn about the dozens of options before you trust anyone's script that you might be provided or that you might find. Understand it well before you use even one of the more well known scripts because such scripts seriously affect log files, database size, and the availability of data itself especially if you only have the Standard Edition or you have blobs in your data and you haven't made it to 2014 yet.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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