I’m putting this script out here so I don’t need to hunt for it later on. It’s useful and common task people ask about often enough.

Original Blog:

http://blogs.msdn.com/b/joaol/archive/2008/01/28/script-to-rebuild-and-reorganize-database-indexes-sql-server-2005.aspx

Note, I fixed some bugs in his original script.

 -- http://blogs.msdn.com/b/joaol/archive/2008/01/28/script-to-rebuild-and-reorganize-database-indexes-sql-server-2005.aspx
/*
Depending on fragmentation index level appropriated action will be taken (no action, rebuild or reorganize)
If average fragmentation is less than 10% no action will be taken, if average fragmentation is between 10% and 30% index will be reorganized, finally if index average fragmentation is greater than 30% index will be rebuilt.
-- Ensure a USE  statement has been executed first.
*/

USE <change_me>
go

SET nocount ON;
go


DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);
DECLARE @dbid SMALLINT;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.

if OBJECT_ID('tempdb..#work_to_do') is not null
begin

drop table #work_to_do

end

SET @dbid = Db_id();
SELECT [object_id] AS objectid,
 index_id AS indexid,
 partition_number AS partitionnum,
 avg_fragmentation_in_percent AS frag,
 page_count
INTO #work_to_do
FROM sys.Dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 -- Allow limited fragmentation
AND index_id > 0 -- Ignore heaps
AND page_count > 25; -- Ignore small tables

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT objectid,
 indexid,
 partitionnum,
 frag
FROM #work_to_do;

OPEN partitions

 FETCH next
 FROM partitions
 INTO @objectid,
 @indexid,
 @partitionnum,
 @frag;

-- Open the cursor.OPEN partitions;
-- Loop through the partitions.
WHILE @@FETCH_STATUS = 0
BEGIN

 
 IF @@FETCH_STATUS < 0 BREAK;
 SELECT @objectname = Quotename(o.NAME),
 @schemaname = Quotename(s.NAME)
 FROM sys.objects AS o
 JOIN sys.schemas AS s
 ON s.schema_id = o.schema_id
 WHERE o.object_id = @objectid;
 
 SELECT @indexname = Quotename(NAME)
 FROM sys.indexes
 WHERE object_id = @objectid
 AND index_id = @indexid;
 
 SELECT @partitioncount = Count (*)
 FROM sys.partitions
 WHERE object_id = @objectid
 AND index_id = @indexid;
 
 -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

 IF @frag < 30.0
 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
 IF @frag >= 30.0
 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
 IF @partitioncount > 1
 SET @command = @command + N' PARTITION=' + Cast(@partitionnum AS NVARCHAR(10));
 EXEC (@command);
 PRINT N'Executed: ' + @command;


 FETCH next
 FROM partitions
 INTO @objectid,
 @indexid,
 @partitionnum,
 @frag;

END
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
go