--***********************SQL Server 索引重建或重组 Begin***********************--
set nocount on
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
declare @flag int;
select @db_id = DB_ID(N'HlData');
--使用游标重新组织指定库中的索引,消除索引碎片
--R_T层游标取出当前数据库所有表
declare R_T cursor
for select name from sys.tables
declare @T varchar(50)
open r_t
fetch next from r_t into @t
while @@fetch_status=0
begin
select @object_id=OBJECT_ID(@t);
--select @flag=0;
--
--print '@t='+@t;
--print '--******************************--';
--R_index游标判断指定表索引碎片情况并优化
declare R_Index cursor for
select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t
join sys.indexes i on i.object_id=t.object_id
join sys.dm_db_index_physical_stats(@db_id,@object_id,null,null,'LIMITED') s on s.object_id=i.object_id and s.index_id=i.index_id
declare @TName varchar(50),@IName varchar(50),@avg int,@str varchar(500)
open r_index
fetch next from r_index into @TName,@Iname,@avg
while @@fetch_status=0
begin
if @avg>=40 --如果碎片大于40,重建索引
begin
set @str='alter index '+rtrim(@Iname)+' on dbo.'+quotename(rtrim(@tname))+' rebuild'
end
else --如果碎片小于40,重新组织索引
begin
set @STR='alter index '+rtrim(@Iname)+' on dbo.'+quotename(rtrim(@tname))+' reorganize'
end
/*
if @avg>=10 --如果碎片大于10
begin
if @flag>0
begin
print '@t='+@t;
select @flag=1;
end;
print '@TName='+@TName;
print '@Iname='+@Iname;
print '@avg='+Convert(varchar(10),@avg);
end
*/
exec (@str) --执行
fetch next from r_index into @TName,@Iname,@avg
end
--结束r_index游标
close r_index
deallocate r_index
fetch next from r_t into @t
end
--结束R_T游标
close r_t
deallocate r_t
set nocount off
--***********************SQL Server 索引重建或重组 End***********************--
set nocount on
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
declare @flag int;
select @db_id = DB_ID(N'HlData');
--使用游标重新组织指定库中的索引,消除索引碎片
--R_T层游标取出当前数据库所有表
declare R_T cursor
for select name from sys.tables
declare @T varchar(50)
open r_t
fetch next from r_t into @t
while @@fetch_status=0
begin
select @object_id=OBJECT_ID(@t);
--select @flag=0;
--
--print '@t='+@t;
--print '--******************************--';
--R_index游标判断指定表索引碎片情况并优化
declare R_Index cursor for
select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t
join sys.indexes i on i.object_id=t.object_id
join sys.dm_db_index_physical_stats(@db_id,@object_id,null,null,'LIMITED') s on s.object_id=i.object_id and s.index_id=i.index_id
declare @TName varchar(50),@IName varchar(50),@avg int,@str varchar(500)
open r_index
fetch next from r_index into @TName,@Iname,@avg
while @@fetch_status=0
begin
if @avg>=40 --如果碎片大于40,重建索引
begin
set @str='alter index '+rtrim(@Iname)+' on dbo.'+quotename(rtrim(@tname))+' rebuild'
end
else --如果碎片小于40,重新组织索引
begin
set @STR='alter index '+rtrim(@Iname)+' on dbo.'+quotename(rtrim(@tname))+' reorganize'
end
/*
if @avg>=10 --如果碎片大于10
begin
if @flag>0
begin
print '@t='+@t;
select @flag=1;
end;
print '@TName='+@TName;
print '@Iname='+@Iname;
print '@avg='+Convert(varchar(10),@avg);
end
*/
exec (@str) --执行
fetch next from r_index into @TName,@Iname,@avg
end
--结束r_index游标
close r_index
deallocate r_index
fetch next from r_t into @t
end
--结束R_T游标
close r_t
deallocate r_t
set nocount off
--***********************SQL Server 索引重建或重组 End***********************--