Index Defrag SP

Last Post 21 May 2009 07:40 AM by dale123. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
dale123
New Member
New Member

--
21 May 2009 07:40 AM
Couls someone help me with this Stored Procedure??

what I'd like to do is have a stored procedure on the server in the master database

Then run it from any database and it will defag all the indexes that are over 30% frgmented.

the only problem is that if say i am connected to the AdventureWorks database and execute

exec master.dbo.uspDefragIndexes - it runs under master, not Adventureworks

I'm trying to get it to run a bit like sp_spaceused.

Thanks

Here is the actual stored procedure


create procedure uspDefragIndexes @Database varchar(50) null
as

declare @DatabaseID int
declare @Table varchar(200)
declare @Index varchar(200)
declare @TSQL nvarchar(2000)
declare @Frag1 bigint
declare @Frag2 bigint

if @Database is null
set @DatabaseID = db_id(@Database)
else
set @DatabaseID = db_id()

set nocount on

declare Frag_Cursor cursor read_only for

select object_name(ifrag.object_id) ,
case when ifrag.index_id = 0 then 'Heap' else si.name end , ifrag.avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(@DatabaseID ,null,null,null,null) ifrag
join sysindexes si on ifrag.object_id = si.id and ifrag.index_id = si.indid
where ifrag.avg_fragmentation_in_percent >= 30
order by ifrag.avg_fragmentation_in_percent desc


open Frag_Cursor
fetch next from Frag_Cursor into @Table , @Index , @Frag1

while @@fetch_status = 0
begin

if (@Index != 'Heap')
begin
set @TSQL = 'ALTER INDEX [' + @Index + '] ON [' + @Table + '] REBUILD'
exec sp_executesql @statement = @TSQL

select @Frag2 = ifrag.avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(@DatabaseID ,null,null,null,null) ifrag
join sysindexes si on ifrag.object_id = si.id and ifrag.index_id = si.indid
where ifrag.object_id = object_id(@Table) and si.name = @Index

print '***** Index [' + @Table + '].[' + @Index + '] defragmented from - ' + convert(varchar , @Frag1) + '% to - ' + convert(varchar , @Frag2) + '% *****'
end

fetch next from Frag_Cursor into @Table , @Index , @Frag1
end

close Frag_Cursor
deallocate Frag_Cursor


Acceptable Use Policy
---