ReBuilding Indexes Automatically Base On Fragmentation Value

I am trying to automate my index rebuild and reorg based off of the percentage of fragmentation level. The first time you run it you have to change the alter proce Sp_NCRNRecreate then change it to Alter proc. It seems to run okay no errors but it doesn't seem to actually rebuild them. Please take a look and let me know if you see a program error somewhere. I would like create this sp to run automatically




use master

GO

Alter PROC sp_NCR_RecreateIndexes @AutoRun bit=0 AS

BEGIN

DECLARE @DatabaseName varchar(128), @SchemaName varchar(128), @objectName varchar(128), @IndexName varchar(128), @PercentFragmented float, @command varchar(max)



SELECT db_name(s.database_id) as DatabaseName, schema_name(o.schema_id) as SchemaName, o.name as TableName, i.name as IndexName, s.avg_fragmentation_in_percent AS PercentFragmented

INTO #IndexesToRebuild

FROM sys.dm_db_index_physical_stats (db_id(), Null, NULL, NULL, NULL) s

INNER JOIN sys.objects o ON s.object_id=o.object_id

INNER JOIN sys.indexes i ON s.object_id=i.object_id AND s.index_id=i.index_id

WHERE s.avg_fragmentation_in_percent > 10.0 AND s.index_id > 0

SELECT * FROM #IndexesToRebuild



DECLARE IndexCursor CURSOR FOR SELECT DatabaseName, SchemaName, TableName, IndexName, PercentFragmented FROM #IndexesToRebuild

OPEN IndexCursor

FETCH IndexCursor INTO @DatabaseName, @SchemaName, @objectName, @IndexName, @PercentFragmented

WHILE @@FETCH_STATUS=0

BEGIN

SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @databaseName + '.' + @schemaname + '.' + @objectName + CASE WHEN @PercentFragmented<30 THEN ' REORGANIZE' ELSE ' REBUILD' END;

print @command

IF @AutoRun=1

EXEC(@command)

FETCH IndexCursor INTO @DatabaseName, @SchemaName, @objectName, @IndexName, @PercentFragmented

END

--SELECT * FROM #IndexStats

END

GO

EXEC sys.sp_MS_marksystemobject sp_NCR_RecreateIndexes

GO

EXEC otis..sp_NCR_RecreateIndexes

EXEC ncrCommon..sp_NCR_RecreateIndexes

GO