转：rebuild索引

declare @objectid int,@indexid int,@partitioncount bigint,@schemaname sysname,@objectname sysname,@indexname sysname,@partitionnum bigint,@partitions bigint,@frag float,@command varchar(1000),@databaseid int
select @databaseid = db_id()
create table #work_to_do(objectid int,indexid int,partitionnum int,frag float)


insert into #work_to_do(objectid,indexid,partitionnum,frag)
select object_id,index_id,partition_number,avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(@databaseid, null, null , null, 'LIMITED')
where index_id > 0 and avg_fragmentation_in_percent > 10
order by index_type_desc
-- declare the cursor for the list of partitions to be processed.
declare partitions cursor for select * from #work_to_do
-- Open the cursor.
open partitions
-- Loop through the partitions.
fetch next from partitions into @objectid, @indexid, @partitionnum, @frag
while @@fetch_status = 0 begin
select @objectname = o.name, @schemaname = s.name from sys.objects as o
inner join sys.schemas as s on s.schema_id = o.schema_id where o.object_id = @objectid
select @indexname = name from sys.indexes where object_id = @objectid and index_id = @indexid
select @partitioncount = count (*) from sys.partitions where object_id = @objectid and index_id = @indexid 
select @command = 'alter index ' + @indexname +' on ' + @schemaname + '.' + @objectname + ' rebuild partition=ALL'
print 'Executed: ' + @command print 'StartTime:' + cast(getdate() as nvarchar)
exec (@command)
print 'EndTime:' + cast(getdate() as nvarchar) print ' '
fetch next from partitions into @objectid, @indexid, @partitionnum, @frag
end
-- free resource
close partitions
deallocate partitions
drop table #work_to_do