UPDATE: This stored procedure has been updated.

This stored procedure defragments indexes in SQL Server 2005. It utilizes sys.dm_db_index_physical_stats, a dynamic management function, to retrieve the fragmentation levels.

If you plan to use DETAILED for @statsMode, you may want to consider running the query that builds the #FragIndex table on a database snapshot.  Kalen Delaney mentions this in the May 2006 edition of SQL Server Magazine (InstantDoc ID 49769).

-------------------------------------------------------------------------------------------
-- OBJECT NAME           : isp_ALTER_INDEX
--
-- AUTHOR                : Tara Kizer
--
-- DATE                  : February 27, 2007
--
-- INPUTS                : @dbName         - name of the database
--                         @statsMode      - mode to use for sys.dm_db_index_physical_stats 
--                         @defragType     - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)
--                         @minFragPercent - minimum fragmentation level
--                         @maxFragPercent - maximum fragmentation level
--                         @minRowCount    - minimum row count
--
-- OUTPUTS               : None
--
-- DEPENDENCIES          : sys.dm_db_index_physical_stats, sys.objects, 
--                         sys.schemas, sys.indexes, sys.partitions
--
-- DESCRIPTION           : Defragments indexes
/*
                           EXEC isp_ALTER_INDEX 
                            @dbName = 'DatabaseName', 
                            @statsMode = 'SAMPLED', 
                            @defragType = 'REBUILD', 
                            @minFragPercent = 10,
                            @maxFragPercent = 100,
                            @ignoreRowCount = 1000
*/
-------------------------------------------------------------------------------------------
CREATE PROC dbo.isp_ALTER_INDEX
(
    @dbName sysname, 
    @statsMode varchar(8) = 'SAMPLED', 
    @defragType varchar(10) = 'REORGANIZE', 
    @minFragPercent int = 25, 
    @maxFragPercent int = 100, 
    @minRowCount int = 0
)
AS
SET NOCOUNT ON
DECLARE
@i int, @objectId int, @objectName sysname, @indexId int, @indexName sysname,
@schemaName sysname, @partitionNumber int, @partitionCount int, @recordCount bigint,
@sql nvarchar(4000), @edition int, @parmDef nvarchar(500)
SELECT @i = 1, @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))
SELECT
IDENTITY(int, 1, 1) AS FragIndexId,
[object_id] AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragPercent,
record_count AS RecordCount,
partition_number AS PartitionNumber
INTO #FragIndex
FROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)
WHERE
avg_fragmentation_in_percent > @minFragPercent AND
avg_fragmentation_in_percent < @maxFragPercent AND
index_id > 0 AND
record_count > @minRowCount
ORDER BY ObjectId
SELECT
@objectId = ObjectId,
@indexId = IndexId,
@recordCount = RecordCount
FROM #FragIndex
WHERE FragIndexId = @i
WHILE @@ROWCOUNT <> 0
BEGIN
SET @sql = '
SELECT @objectName = o.[name], @schemaName = s.[name]
FROM ' + @dbName + '.sys.objects o
JOIN ' + @dbName + '.sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.[object_id] = @objectId'
<span class="kwrd">SET</span> @parmDef = N<span class="str">&#39;@objectId int, @objectName sysname OUTPUT, @schemaName sysname OUTPUT&#39;</span>

<span class="kwrd">EXEC</span> sp_executesql 
    @<span class="kwrd">sql</span>, @parmDef, @objectId = @objectId, 
    @objectName = @objectName <span class="kwrd">OUTPUT</span>, @schemaName = @schemaName <span class="kwrd">OUTPUT</span>

<span class="kwrd">SET</span> @<span class="kwrd">sql</span> = <span class="str">&#39;
    SELECT @indexName = [name]
    FROM &#39;</span> + @dbName + <span class="str">&#39;.sys.indexes
    WHERE [object_id] = @objectId AND index_id = @indexId AND is_disabled = 0&#39;</span>

<span class="kwrd">SET</span> @parmDef = N<span class="str">&#39;@objectId int, @indexId int, @indexName sysname OUTPUT&#39;</span>

<span class="kwrd">EXEC</span> sp_executesql 
    @<span class="kwrd">sql</span>, @parmDef, @objectId = @objectId, @indexId = @indexId, 
    @indexName = @indexName <span class="kwrd">OUTPUT</span>

<span class="kwrd">SET</span> @<span class="kwrd">sql</span> = <span class="str">&#39;
    SELECT @partitionCount = COUNT(*)
    FROM &#39;</span> + @dbName + <span class="str">&#39;.sys.partitions
    WHERE [object_id] = @objectId AND index_id = @indexId&#39;</span>

<span class="kwrd">SET</span> @parmDef = N<span class="str">&#39;@objectId int, @indexId int, @partitionCount int OUTPUT&#39;</span>

<span class="kwrd">EXEC</span> sp_executesql 
    @<span class="kwrd">sql</span>, @parmDef, @objectId = @objectId, @indexId = @indexId, 
    @partitionCount = @partitionCount <span class="kwrd">OUTPUT</span>

<span class="kwrd">SET</span> @<span class="kwrd">sql</span> = <span class="str">&#39;ALTER INDEX [&#39;</span> + @indexName + <span class="str">&#39;] ON [&#39;</span> + @dbName + <span class="str">&#39;].[&#39;</span> + 
    @schemaName + <span class="str">&#39;].[&#39;</span> + @objectName + <span class="str">&#39;] &#39;</span> + @defragType

<span class="rem">-- Developer and Enterprise have the ONLINE = ON option for REBUILD</span>
<span class="kwrd">IF</span> @defragType = <span class="str">&#39;REBUILD&#39;</span> <span class="kwrd">AND</span> @edition = 3
    <span class="kwrd">SET</span> @<span class="kwrd">sql</span> = @<span class="kwrd">sql</span> + <span class="str">&#39; WITH (ONLINE = ON)&#39;</span>

<span class="kwrd">IF</span> @partitionCount &gt; 1
    <span class="kwrd">SET</span> @<span class="kwrd">sql</span> = @<span class="kwrd">sql</span> + <span class="str">&#39; PARTITION = &#39;</span> + <span class="kwrd">CAST</span>(@partitionNumber <span class="kwrd">AS</span> <span class="kwrd">varchar</span>(10))

<span class="kwrd">EXEC</span> (@<span class="kwrd">SQL</span>)

<span class="kwrd">SET</span> @i = @i + 1

<span class="kwrd">SELECT</span> 
    @objectId = ObjectId, 
    @indexId = IndexId, 
    @recordCount = RecordCount
<span class="kwrd">FROM</span> #FragIndex
<span class="kwrd">WHERE</span> FragIndexId = @i

END
DROP TABLE #FragIndex