Index fragmentation.

• Satya_0000

  Mr or Mrs. 500

  Points: 544

  More actions

  January 24, 2012 at 11:41 pm

  #263246

  I want to get the index details for all the tables in paricular database and copy it to a table for analysis.

• umasingh

  Ten Centuries

  Points: 1182

  More actions

  January 24, 2012 at 11:44 pm

  #1438096

  You can insert the resultset of below query to any table.

  SELECT ps.database_id, ps.OBJECT_ID,

  ps.index_id, b.name,

  ps.avg_fragmentation_in_percent

  FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

  INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID

  AND ps.index_id = b.index_id

  WHERE ps.database_id = DB_ID()

  ORDER BY ps.OBJECT_ID

  GO

• Satya_0000

  Mr or Mrs. 500

  Points: 544

  More actions

  January 25, 2012 at 12:06 am

  #1438108

  this will help me,Thank you very much .

• vinothraj

  Ten Centuries

  Points: 1349

  More actions

  January 25, 2012 at 12:07 am

  #1438109

  as said earlier, all details are in sys.Indexes.

• umasingh

  Ten Centuries

  Points: 1182

  More actions

  January 25, 2012 at 12:09 am

  #1438111

  Satya_0000 (1/25/2012)

  ---

  this will help me,Thank you very much .

  Welcome

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  January 26, 2012 at 7:19 am

  #1438769

  Get and learn to use the AMAZING, FREE maintenance scripts from ola.hallengren.com. Nice documentation with it too.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service