sp_DefragIndexes --For SQL server 2005--The Best

USE [master]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- This stored procedure checks all indexes in the current
-- database and performs either offline or online defragmentation
-- according to the specified thresholds.
-- The stored procedure also updates statistics for indexes in which the last update
-- time is older than the specified threshold.
-- Parameters:
-- @onlineDefragThreshold specifies minimum percentage of fragmentation
-- to perform online defragmentation (default 10%).
-- @offlineDefragThreshold specifies minimum percentage of fragmentation
-- to perform offline defragmentation (default 30%).
-- @updateStatsThreshold specifies the number of days since the last statistics update
-- which should trigger updating statistics (default 7 days).
-- =============================================
CREATE PROCEDURE [dbo].[sp_DefragIndexes]
(
@databaseName sysname = null,
@onlineDefragThreshold float = 10.0,
@offlineDefragThreshold float = 30.0,
@updateStatsThreshold int = 7
)

AS
BEGIN

IF @databasename is null
BEGIN
RETURN;
END

DECLARE @SQL nvarchar(4000)
SET @SQL = 'USE '+ @databasename +'

set nocount on
DECLARE @objectid int
DECLARE @indexid int
DECLARE @frag float
DECLARE @command varchar(8000)
DECLARE @schemaname sysname
DECLARE @objectname sysname
DECLARE @indexname sysname

declare @AllIndexes table (objectid int, indexid int, fragmentation float)

declare @currentDdbId int
select @currentDdbId = DB_ID()

insert into @AllIndexes
SELECT
object_id, index_id, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@currentDdbId, NULL, NULL , NULL, ''LIMITED'')
WHERE index_id > 0

DECLARE indexesToDefrag CURSOR FOR SELECT * FROM @AllIndexes

OPEN indexesToDefrag

-- Loop through the partitions.
FETCH NEXT
FROM indexesToDefrag
INTO @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @schemaname = s.name
FROM sys.objects AS o
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

IF @frag > @onlineDefragThreshold
BEGIN
IF @frag < @offlineDefragThreshold
BEGIN
SELECT @command = ''ALTER INDEX '' + @indexname + '' ON '' +
@schemaname + ''.'' + object_name(@objectid) +
'' REORGANIZE''
EXEC (@command)
END

IF @frag >= @offlineDefragThreshold
BEGIN
SELECT @command = ''ALTER INDEX '' +
@indexname +'' ON '' + @schemaname + ''.'' +
object_name(@objectid) + '' REBUILD''
EXEC (@command)
END;
PRINT ''Executed '' + @command
END

IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, -@updateStatsThreshold, getdate())
BEGIN
SELECT @command = ''UPDATE STATISTICS '' + @schemaname + ''.'' + object_name(@objectid) +
'' '' + @indexname +'' WITH RESAMPLE''
EXEC (@command)

PRINT ''Executed '' + @command
END

FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @frag

END

CLOSE indexesToDefrag;
DEALLOCATE indexesToDefrag'

DECLARE @Params nvarchar(4000)
SET @Params = N'
@onlineDefragThreshold float,
@offlineDefragThreshold float,
@updateStatsThreshold int'

EXECUTE sp_executesql @SQL,
@Params,
@onlineDefragThreshold=@onlineDefragThreshold,
@offlineDefragThreshold=@offlineDefragThreshold,
@updateStatsThreshold=@updateStatsThreshold;
END
-------------------------------------------
exec sp_DefragIndexes 'AdventureWorks'