declare @fragmentedIndexes as table ( ID int identity(1,1), [objectid] int NULL, [indexid] int NULL, [partitionnum] int NULL, [frag] float NULL ) declare @count int declare @i tinyint=1 declare @schemaname sysname; declare @objectname sysname; declare @indexname sysname; declare @objectid int; declare @indexid int; declare @partitionnum bigint; declare @partitioncount bigint; declare @command as Nvarchar(3000) insert into @fragmentedIndexes select object_id as objectid, index_id as indexid, partition_number as partitionnum, avg_fragmentation_in_percent as frag from sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') --Arguments 1(Database_ID,object_Id,Index_ID,partition,mode where avg_fragmentation_in_percent >=30.0 AND index_id> 0; select @count=Count(*) from @fragmentedIndexes --Get Total Count while(@i<=@count) begin select @objectid=objectid, @indexid=indexid, @partitionnum=partitionnum from @fragmentedIndexes where ID=@i --Get tableName and its schema select @objectname=o.name, @schemaname=c.name from sys.objects o inner join sys.schemas c on (o.schema_id=c.schema_id) where o.object_id=@objectid --Get Index Name select @indexname=name from sys.indexes where index_id=@indexid and object_id=@objectid --Get Partition Count select @partitioncount=count(*) from sys.partitions where object_id=@objectid and index_id=@indexid select @command= 'alter index [' + @indexname + '] on [' + @schemaname + '].[' + @objectname + '] rebuild' if(@partitioncount>1) select @command=@command + ' partition=' + convert(Char,@partitionnum); print @command; exec(@command); --Increment Count set @i=@i+1 end