SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_DefragIndexes')
	BEGIN
		DROP  Procedure  dbo.proc_DefragIndexes
	END

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 90%).
--	@updateStatsThreshold specifies the number of days since the last statistics update
--	which should trigger updating statistics (default 7 days).
-- =============================================
CREATE PROCEDURE dbo.proc_DefragIndexes 
(
	@onlineDefragThreshold float = 10.0,
	@offlineDefragThreshold float = 90.0,
	@updateStatsThreshold int = 7
)
	
AS
BEGIN
	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;
END
GO