When were the indexes last rebuilt?

• sunny.tjk

  SSCertifiable

  Points: 5041

  More actions

  August 8, 2011 at 2:44 pm

  #241810

  Hi everyone,

  Just curious if anyone has a script to see when were the indexes last rebuilt in a database? I've a query which uses STATS_DATE ( A.id , indid ) function. I think it gives the statistics updated date but not the index created date.

  Thanks,

  Sunil.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  August 8, 2011 at 4:21 pm

  #1365581

  This will return the time indexes were last updated:

  Whoops:

  STAT_DATE Returns the date that the statistics for the specified index were last updated.

  Edited first line of text to:

  This will return the time indexes statistics were last updated

  SELECT 'Index Name' = i.name, OBJECT_NAME(i.id) AS 'Table Name',

  Case Indid

  WHEN 1 THEN 'Clustered'

  ELSE 'Non-clustered'

  End 'Type',

  'Last Updated' = STATS_DATE(i.id, i.indid),rowmodctr AS

  '# Rows inserted deleted or updated', i.keys

  FROM sysobjects o, sysindexes i

  WHERE o.id = i.id AND (o.type <> 'S' AND indid <> 0 AND indid <> 255)

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]