--
------------------------------------------------------------------------------
-- ensure a use databse statement has been executed first.
-- ------------------------------------------------------------------------------
use DBName
set nocount on
declare @objectid int
, @indexid int
, @partitioncount bigint
, @schemaname sysname
, @objectname sysname
, @indexname sysname
, @partitionnum bigint
, @partitions bigint
, @frag float
, @command varchar ( 1000 )
, @db_id int
set @db_id = db_id ()
print @db_id
select objectid = object_id
,indexid = index_id
,partitionnum = partition_number
,frag = avg_fragmentation_in_percent
into #work_to_do
from sys.dm_db_index_physical_stats( @db_id , null , null , null , ' LIMITED ' )
where avg_fragmentation_in_percent > 5.0 and index_id > 0
-- 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
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
if @frag < 30.0 begin
select @command = ' alter index [ ' + @indexname + ' ] on '
+ @schemaname + ' . ' + @objectname + ' reorganize '
if @partitioncount > 1
select @command = @command + ' partition= ' + convert ( char , @partitionnum )
end
if @frag >= 30.0 begin
select @command = ' alter index [ ' + @indexname + ' ] on '
+ @schemaname + ' . ' + @objectname + ' rebuild '
if @partitioncount > 1
select @command = @command + ' partition= ' + convert ( char , @partitionnum )
end
exec ( @command )
print ' Executed: ' + @command
fetch next from partitions into @objectid , @indexid , @partitionnum , @frag
end
-- free resource
close partitions
deallocate partitions
drop table #work_to_do
-- ensure a use databse statement has been executed first.
-- ------------------------------------------------------------------------------
use DBName
set nocount on
declare @objectid int
, @indexid int
, @partitioncount bigint
, @schemaname sysname
, @objectname sysname
, @indexname sysname
, @partitionnum bigint
, @partitions bigint
, @frag float
, @command varchar ( 1000 )
, @db_id int
set @db_id = db_id ()
print @db_id
select objectid = object_id
,indexid = index_id
,partitionnum = partition_number
,frag = avg_fragmentation_in_percent
into #work_to_do
from sys.dm_db_index_physical_stats( @db_id , null , null , null , ' LIMITED ' )
where avg_fragmentation_in_percent > 5.0 and index_id > 0
-- 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
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
if @frag < 30.0 begin
select @command = ' alter index [ ' + @indexname + ' ] on '
+ @schemaname + ' . ' + @objectname + ' reorganize '
if @partitioncount > 1
select @command = @command + ' partition= ' + convert ( char , @partitionnum )
end
if @frag >= 30.0 begin
select @command = ' alter index [ ' + @indexname + ' ] on '
+ @schemaname + ' . ' + @objectname + ' rebuild '
if @partitioncount > 1
select @command = @command + ' partition= ' + convert ( char , @partitionnum )
end
exec ( @command )
print ' Executed: ' + @command
fetch next from partitions into @objectid , @indexid , @partitionnum , @frag
end
-- free resource
close partitions
deallocate partitions
drop table #work_to_do