22,206
社区成员
发帖
与我相关
我的任务
分享
declare @schemaname varchar(255)
declare @tablename varchar(255)
declare @indexname varchar(255)
declare @fragmentation float
declare @command varchar(255)
declare tablecursor cursor for select SCHEMA_NAME(CAST(OBJECTPROPERTYEX(i.object_id,'schemaId')as int)),object_name(i.object_id),i.name,ps.avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(),null,null,null,null)as ps join sys.indexes as i on ps.object_id=i.object_id and ps.index_id=i.index_id where avg_fragmentation_in_percent>30
open tablecursor
fetch next from tablecursor into @schemaname,@tablename,@indexname,@fragmentation
while @@fetch_status=0
begin
print @schemaname+'.'+@tablename+'.'+@indexname+'is'+cast(@fragmentation as varchar)+'% fragmentented'
set @command='alter index '+rtrim(@indexname)+' on['+isnull(@schemaname,'dbo')+'].['+rtrim(@tablename)+'] rebuild'
PRINT @command
--exec (@command)
fetch next from tablecursor into @schemaname,@tablename,@indexname,@fragmentation
end
close tablecursor
deallocate tablecursor