CREATE PROCEDURE sjh_defragment_indexes
(
	@maxfrag FLOAT
)
AS

/* Simon Hughes 15 May 2010

For SQL 2005+ 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 5
*/

SET NOCOUNT ON
SET XACT_ABORT ON

-- Check this is not being run in a system database
IF DB_NAME() IN ('master', 'msdb', 'model', 'tempdb') 
BEGIN
    PRINT 'This procedure should not be run in system databases.'
    RETURN
END

-- Check fragmentation
SELECT  OBJECT_NAME(i.object_id) AS TableName,
        i.name AS TableIndexName,
        phystat.avg_fragmentation_in_percent,
        phystat.fragment_count,
        'DBCC INDEXDEFRAG (0, [' + OBJECT_NAME(i.object_id) + '], ' + i.name + ') WITH NO_INFOMSGS' AS execstr
INTO    [#frag]
FROM    sys.dm_db_index_physical_stats(DB_ID('bybox'), NULL, NULL, NULL, 'LIMITED') phystat
        INNER JOIN sys.indexes i
            ON i.object_id = phystat.object_id
               AND i.index_id = phystat.index_id
WHERE   i.name IS NOT NULL
        AND phystat.avg_fragmentation_in_percent > @maxfrag
ORDER BY phystat.avg_fragmentation_in_percent DESC

SELECT * FROM [#frag]

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


-- Begin defrag
DECLARE @execstr NVARCHAR(1024)
DECLARE cc CURSOR FAST_FORWARD FOR
	SELECT execstr FROM [#frag]

OPEN cc
FETCH NEXT FROM cc INTO @execstr

WHILE @@FETCH_STATUS = 0 
BEGIN
    EXEC sp_executesql @execstr

    FETCH NEXT FROM cc INTO @execstr
END

-- Close and deallocate the cursor
CLOSE cc
DEALLOCATE cc

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

-- Delete the temporary table
DROP TABLE [#frag]
GO