Index Date

• Tara-1044200

  SSCoach

  Points: 15951

  More actions

  March 13, 2010 at 9:09 am

  #216661

  How can i find index create date and size of all indexes in a given database.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  March 13, 2010 at 11:44 am

  #1133243

  Here is the code that might do what you require:

  --INdex Size Author: CirquedeSQLeil (Jason ) on SCC

  Declare @DBName varchar(50)

  Set @DBName = 'Yourdatabasename'

  Select Db_name(ps.database_id) as DBName,object_name(ps.object_id) as TableName,I.name as IndexName

  ,sum(isnull(ps.avg_record_size_in_bytes,0) * isnull(ps.record_count,0)) as IndexSizeBytes

  ,sum((isnull(ps.avg_record_size_in_bytes,0) * isnull(ps.record_count,0)) /1024/1024) as IndexSizeMB

  From sys.dm_db_index_physical_stats(DB_ID(@DBName), Null, NULL, NULL , 'DETAILED') ps

  Inner Join sys.indexes I

  On I.object_id = ps.object_id

  And I.index_id = ps.index_id

  Group By ps.database_id,ps.object_id,i.Name

  Order by IndexSizeMB desc

  It does not return the date the index was created.

  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]