自动维护索引--联机重建或组织索引(二)
ame,@TableName,@IndexName,@SchemaName,@PctFrag
while @@FETCH_STATUS=0
begin
if @PctFrag between 20.0 and 40.0
begin
set @Defrag=N' ALTER INDEX '+@IndexName+' ON'+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REORGANIZE'--重新组织索引页不删除索引
EXEC SP_EXECUTESQL @Defrag
end www.2cto.com
else if @PctFrag>40.0
begin
SET @Defrag=N' ALTER INDEX '+@IndexName+' ON'+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REBUILD WITH (ONLINE = ON )'--联机重建索引。即不锁定表重新创建索引
EXEC SP_EXECUTESQL @Defrag
end
fetch next from frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag
end
close frg_cur
deallocate frg_cur
end
set nocount off