转: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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值