CREATE PROCEDURE sjh_defragment_indexes_2008
(
	@maxfrag FLOAT
)
AS

/* Simon Hughes 15 May 2010

For SQL 2008+ only
This stored procedure checks index fragmentation in a database and defragments
indexes whose fragmentation fall above a specified threshold: @maxfrag

Must be run in the database to be defragmented.

exec sjh_defragment_indexes_2008 5
*/

SET NOCOUNT ON
SET XACT_ABORT ON

-- Write start time for information purposes
PRINT 'Started defragmenting at: ' + CONVERT(VARCHAR, GETDATE())

-- Check fragmentation
DECLARE @execstr NVARCHAR(255)
DECLARE lcsr CURSOR local fast_forward READ_ONLY FOR
	SELECT  'ALTER INDEX [' + i.name + '] ON [' + so.name + '] REORGANIZE;' AS execstr
	FROM    sys.sysobjects AS so
			INNER JOIN sys.dm_db_index_physical_stats(DB_ID('bybox'), NULL, NULL, NULL, 'LIMITED') AS phystat
			INNER JOIN sys.indexes AS i
				ON i.object_id = phystat.object_id
				   AND i.index_id = phystat.index_id
				ON so.name = OBJECT_NAME(i.object_id)
	WHERE   (i.name IS NOT NULL)
			AND (so.type = 'U')
			AND (phystat.avg_fragmentation_in_percent > @maxfrag)

OPEN lcsr
FETCH NEXT FROM lcsr INTO @execstr

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC sp_executesql @execstr
	FETCH NEXT FROM lcsr INTO @execstr
END

CLOSE lcsr
DEALLOCATE lcsr

-- Report on finish time for information purposes
PRINT 'Finished defragmenting at: ' + CONVERT(VARCHAR, GETDATE())
GO