Script to Rebuild indexes based on the average fragmentation percentage value -- SQL Server 2005

I created this stored procedure to Rebuild indexes based on the average fragmentation percentage value. It works pretty well for me. I thought of sharing this with you all. You could limit the rebuild based on the page count as well if you like./*****************************************************************************************************************
Description: This stored procedure rebuilds the indexes based on the average fragment percentage value from
sys.dm_db_index_physical_stats dynamic view. It first captures all the indexes with average fragment
percentage value >= 10 and then if the value is 30 or below it does the REORGANIZE and REBUILD for
the indexes with value above 30. It also runs UPDATE STATISICS with REORGANIZE.
Parameter: @db_name - name of the database to rebuild the indexes on.
********************************************************************************************************************/CREATE
PROC [dbo].[usp_DB_Reindex](@db_name VARCHAR(100))AS
SET NOCOUNT ON
DECLARE
@dbid VARCHAR(5)SELECT
@dbid = database_idFROM
sys.databasesWHERE
name = @db_name--SELECT @dbidDECLARE
@Query VARCHAR(5000)SET @Query = 'USE ' + @db_name + '
SELECT o.object_id,
i.index_id,
LTRIM(RTRIM(s.name))+ ''.[''+LTRIM(RTRIM(o.name))+'']'',
i.name,
CASE WHEN (p.avg_fragmentation_in_percent <= 30)
THEN ''REORGANIZE''
ELSE ''REBUILD''
END,
CASE WHEN (p.avg_fragmentation_in_percent > 30)
THEN ''WITH (ONLINE = ON)''
ELSE ''''
END,
p.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats ('+ @dbid+ ', NULL, NULL, NULL, NULL) p
INNER JOIN
sys.indexes i
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN
sys.objects o
ON o.object_id = p.object_id
AND o.type = ''u''
AND o.name not like ''sys%''
AND o.name not like ''MS%''
INNER JOIN
sys.schemas s
ON s.schema_id = o.schema_id
AND i.name IS NOT NULL
AND p.avg_fragmentation_in_percent >=10
ORDER BY p.avg_fragmentation_in_percent DESC'CREATE
TABLE #tmp_table (Counter
INT IDENTITY(1,1),ObjectID
INT,IndexID
INT,Table_Name
VARCHAR(200),Index_Name
VARCHAR(200),Perform_Action
VARCHAR(20),Options
VARCHAR(50),Avg_frag_percent_before DECIMAL(16,13)
)INSERT
INTO #tmp_table(
ObjectID, IndexID, Table_Name, Index_Name, Perform_Action,Options,Avg_frag_percent_before)EXEC
(@Query)--If an index contains a colums with data type image, text or xml, rebuild the indexes offlineDECLARE
@Query1 VARCHAR(5000)SET @Query1 = 'USE ' + @db_name + '
UPDATE tt
SET Options = ''''
FROM #tmp_table tt
INNER JOIN
sys.columns c
ON c.object_id = tt.ObjectID
INNER JOIN
sys.types t
ON t.system_type_id = c.system_type_id
AND t.user_type_id = c.user_type_id
AND t.name in (''image'',''text'',''xml'')
AND tt.Perform_Action = ''REBUILD'''EXEC (@Query1) DECLARE @max_counter INT
SET
@max_counter = (SELECT Max(Counter) FROM #tmp_table)DECLARE @counter INT
SET
@counter = 1DECLARE
@String VARCHAR(2000)DECLARE @ObjectID INT
DECLARE
@IndexID INTDECLARE
@Table_Name VARCHAR(200)DECLARE
@Index_Name VARCHAR(200)DECLARE
@Action VARCHAR(20)DECLARE @Options VARCHAR(50) WHILE
@counter > 0 AND @counter <= @max_counterBEGIN
SELECT @ObjectID = ObjectID, @IndexID = IndexID, @Action = Perform_Action, @Table_Name = Table_Name, @Index_Name = Index_Name, @Options = OptionsFROM #tmp_table WHERE Counter = @counter
IF @Action = 'REBUILD' AND @Options <> ''
BEGINSET @String = 'USE ' + @db_name + ' ALTER INDEX '+ @Index_Name + ' ON ' + @Table_Name + ' ' + @Action + ' ' + @Options
EXEC(@String)
END
ELSE IF @Action = 'REBUILD' AND @Options = ''
BEGINSET @String = 'USE ' + @db_name + ' ALTER INDEX '+ @Index_Name + ' ON ' + @Table_Name + ' ' + @Action
EXEC(@String)
END
ELSE
BEGINSET @String = 'USE ' + @db_name + ' ALTER INDEX '+ @Index_Name + ' ON ' + @Table_Name + ' ' + @Action
EXEC(@String)
EXEC('USE ' + @db_name + ' UPDATE STATISTICS ' + @table_name + ' ' + @index_name)
END
SET @counter = @counter + 1END
DROP TABLE #tmp_table