游标使用系列二
索引重建的情况
对一个数据库所有的表执行索引重建：
Declare T_cursor cursor
for
select name from sys.tables
Declare @T_name varchar(50)
open T_cursor
fetch next from T_cursor into @T_name;
while @@fetch_status=0
Begin
Declare I_cursor cursor
for
select a.name,b.name,c.avg_fragmentation_in_percent from sys.tables a
join sys.indexes b on a.object_id=b.object_id
join sys.dm_db_index_physical_stats(DB_ID(N'student_score'),object_id(@T_name),null,null,null) c on b.object_id=c.object_id and b.index_id=c.index_id;
Declare @Table_name nvarchar(100),@Index_name nvarchar(100),@Avg_Fra int,@SQL nvarchar(100)
open I_cursor
fetch next from I_cursor into @Table_name,@Index_name,@Avg_Fra
while @@FETCH_STATUS=0
Begin
if @Avg_Fra>30
begin
set @SQL='Alter index '+@Index_name+' on dbo.'+@Table_name+' Rebuild with (fillfactor=75)'
end
else
Begin
set @SQL='Alter index '+@Index_name+' on dbo.'+@Table_name+' Reorganize'
end
Print @SQL
Exec (@SQL)
fetch next from I_cursor into @Table_name,@Index_name,@Avg_Fra
end
close I_cursor
deallocate I_cursor
fetch next from T_cursor into @T_name;
end
close T_cursor;
deallocate T_cursor;
对所有数据库所有的表执行索引重建(对索引碎片比率进行判断后执行重建或重组)：
Declare DB_cursor cursor
for
select name from sys.databases where name name in
(N'school_new',N'student_score')
declare @DBname varchar(50)
open DB_cursor
fetch next from DB_cursor into @DBname;
while @@FETCH_STATUS=0
Begin
declare @CMD varchar(1000)
set @CMD=
'use ['+@DBname+'];'+'
Declare T_cursor cursor
for
select name from sys.tables'
Exec (@CMD)
Declare @T_name varchar(50)
open T_cursor;
fetch next from T_cursor into @T_name;
while @@fetch_status=0
Begin
Declare I_cursor cursor
for
select a.name,b.name,c.avg_fragmentation_in_percent from sys.tables a
join sys.indexes b on a.object_id=b.object_id
join sys.dm_db_index_physical_stats(DB_ID(@DBname),object_id(@T_name),null,null,null) c on b.object_id=c.object_id and b.index_id=c.index_id;
Declare @Table_name nvarchar(100),@Index_name nvarchar(100),@Avg_Fra int,@SQL nvarchar(100)
open I_cursor
fetch next from I_cursor into @Table_name,@Index_name,@Avg_Fra
while @@FETCH_STATUS=0
Begin
if @Avg_Fra>30
begin
set @SQL=
'Alter index '+@Index_name+' on dbo.'+@Table_name+ ' Rebuild with (fillfactor=75)'
end
else
Begin
set @SQL=
'Alter index '+@Index_name+' on dbo.'+@Table_name+' REORGANIZE'
end
Print @SQL
Exec (@SQL)
fetch next from I_cursor into @Table_name,@Index_name,@Avg_Fra
end
close I_cursor
deallocate I_cursor
fetch next from T_cursor into @T_name;
end
close T_cursor;
deallocate T_cursor;
fetch next from DB_cursor into @DBname;
end
close DB_cursor
deallocate DB_cursor;
