Defragment all indexes in the current database

Use this script to defragment every index on every table in the current database.

declare @workFlow table (
    WorkID  int identity (1, 1)
    ,TableName sysname
    ,IndexName sysname
    ,unique(TableName, IndexName)
    )
declare @startID int
declare @endID  int
declare @curID  int
declare @curTableName sysname
declare @curIndexName sysname

set nocount on

insert @workFlow
 (
 TableName
 ,IndexName
 )
 select dbo.sysobjects.[name] as TableName
  ,dbo.sysindexes.[name] as IndexName
  from dbo.sysobjects
   inner join dbo.sysindexes
     on dbo.sysindexes.[id] = dbo.sysobjects.[id]
  where (dbo.sysobjects.xtype = 'U')
   and (dbo.sysindexes.impid = 0)
  order by dbo.sysobjects.[name]

select @startID
  = min(WorkID)
 ,@endID
  = max(WorkID)
 from @workFlow workFlow

set @curID = @startID

while (@curID <= @endID)
 begin
  select @curTableName
    = workFlow.TableName
   ,@curIndexName
    = workFlow.IndexName
   from @workFlow workFlow
   where (workFlow.WorkID = @curID)

  dbcc indexdefrag (0, @curTableName, @curIndexName)
   with no_infomsgs

  set @curID = @curID + 1
 end

If you need to rebuild the indexes, replace the 'dbcc indexdefrag ...' statement with this one:

dbcc dbreindex (@curTableName, @curIndexName)
 with no_infomsgs

Also make sure you read this must-read white paper on index defragmentation by Mike Ruthruff: Microsoft SQL Server 2000 Index Defragmentation Best Practices.

ML