It was working fine until it came across an index on a view earlier, since i referenced sys.tables instead of sys.objects it didn't pick these up. This is now resolved. Then I noticed it didn't take into account if an index had a '.' in the name of it.. so this is where this change has come from..

--Create temp table for list of indexes

CREATE TABLE #IndexFrag(

database_id int,

object_ID int,

index_id int,

name ntext,

page_count int,

avg_fragmentation_In_Percent real )

--Fill the table with indexes with a page count higher than 10 and fragmented more than 5%

insert into #IndexFrag (database_id, object_ID, index_id, name, page_count, avg_fragmentation_In_Percent)

SELECT ps.database_id, ps.OBJECT_ID,

ps.index_id, b.name, ps.page_count,

ps.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID

AND ps.index_id = b.index_id

WHERE ps.database_id = DB_ID() and ps.page_count > 10 and ps.avg_fragmentation_in_percent > 5

ORDER BY ps.OBJECT_ID

--Selecting all index's between 5% and 40% fragmented

declare @cnt int

declare @Result varchar(200)

declare @cmd nvarchar(500)

declare @tablename nvarchar(500)

declare @objectID int

declare FindFragment cursor for

SELECT object_id, '['+name+']' FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')

--Cursor to go through each index which are between 5% and 40% fragmented and rebuild

open FindFragment

fetch next from FindFragment into @ObjectID, @result

while @@fetch_status = 0

BEGIN

select @tablename = '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' FROM sys.indexes i

INNER JOIN sys.objects AS t ON i.object_id = t.object_id WHERE i.name = @Result AND i.object_id = @objectID

set @cmd = N'ALTER Index ' + @result + ' on ' + @tablename + ' REORGANIZE '

print @cmd

--EXEC sp_executeSQL @cmd

fetch next from FindFragment into @objectID, @result

END

close FindFragment

deallocate FindFragment

--drop table #IndexFrag

declare FindFragment cursor for

SELECT object_id, '['+name+']' FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 40) and (name not like 'null')

--Cursor to go through each index which are over 40% fragmented and rebuild

open FindFragment

fetch next from FindFragment into @objectID, @result

while @@fetch_status = 0

BEGIN

select @tablename = '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' FROM sys.indexes i

INNER JOIN sys.objects AS t ON i.object_id = t.object_id WHERE i.name = @Result AND i.object_id = @objectID

set @cmd = N'ALTER Index ' + @result + ' on ' + @tablename + ' REBUILD '

PRINT @cmd

--EXEC sp_executeSQL @cmd

fetch next from FindFragment into @objectID, @result

END

close FindFragment

deallocate FindFragment

drop table #IndexFrag

Thats the full script, works well for what I need, but changing it to '[' + name + ']' brings back the error

''The data types varchar and ntext are incompatible in the add operator.''

We can't do a select as in this scenario can we?