How to find out which table need to REBUILD INDEX?

• Leo-696352

  SSCrazy

  Points: 2708

  More actions

  October 30, 2008 at 7:03 am

  #195299

  Hi Guys,

  This is a code I wrote in store procedure -

  DECLARE @TableName varchar(255)

  DECLARE TableCursor CURSOR FOR

  SELECT table_name

  FROM information_schema.tables

  WHERE table_type = 'base table'

  OPEN TableCursor

  FETCH NEXT FROM TableCursor INTO @TableName

  WHILE @@FETCH_STATUS = 0

  BEGIN

  DBCC DBREINDEX(@TableName,' ',90)

  FETCH NEXT FROM TableCursor INTO @TableName

  END

  CLOSE TableCursor

  DEALLOCATE TableCursor

  What it does is, loop through all the tables in the datatabase and rebuild the indexes for ALL TABLES.

  What I want is I want to rebuild the Index which is ONLY 'Logical Scan Frag is > 30% '. Basically I want to do 2 or 3 tables out of 100 tables which I don't needed.

  Any way can I find out which one is really need to ReIndex?

  Thanks.

• Jerry Hung

  SSChampion

  Points: 12968

  More actions

  October 30, 2008 at 7:49 am

  #891784

  There are a lot of scripts out there that kinda does this, might benefit from them (although they seem to work for SQL 2005 and above, not 200)

  http://sqlcommunity.com/Default.aspx?tabid=275&id=288

  http://blog.sqlauthority.com/2008/03/04/sql-server-2005-a-simple-way-to-defragment-all-indexes-in-a-database-that-is-fragmented-above-a-declared-threshold/

  Usually involves sys.dm_db_index_physical_stats or DBCC SHOWCONTIG

  SQLServerNewbieMCITP: Database Administrator SQL Server 2005

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737364

  More actions

  October 30, 2008 at 7:55 am

  #891795

  look at this

  http://www.sqlservercentral.com/scripts/Miscellaneous/30473/

• Leo-696352

  SSCrazy

  Points: 2708

  More actions

  October 30, 2008 at 4:09 pm

  #892110

  Hi Steve,

  Thanks. This is what I required.