--Use 'Database' BEGIN TRAN declare @name1 varchar(150) declare @name2 varchar(150) declare CRS Cursor for SELECT OBJECT_NAME(ind.OBJECT_ID) AS DBtable, ind.name AS IndexName FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 10 and ind.name is not null ORDER BY indexstats.avg_fragmentation_in_percent DESC; open CRS fetch Next from CRS into @name1,@name2 While (@@FETCH_STATUS=0) begin select @name1 as [Table name],@name2 as [Index name]; ALTER INDEX @name2 ON @name1 REBUILD PARTITION = ALL WITH (fillfactor=85, PAD_INDEX = on, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = on, SORT_IN_TEMPDB = on ) fetch next from CRS into @name1,@name2; end close CRS Deallocate CRS ROLLBACK
Error message
Incorrect syntax near the keyword 'with' (for rebuild the indices). If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon