Due to the problem mr_mist found in the DBCC DBREINDEX stored procedure, I have an updated version of the DBCC INDEXDEFRAG sproc as well:
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_DBCC_INDEXDEFRAG
--
-- AUTHOR : Tara Duggan
-- DATE : May 11, 2004
--
-- INPUTS : @dbName - name of the database
-- OUTPUTS : None
-- DEPENDENCIES : None
--
-- DESCRIPTION : This stored procedure runs DBCC INDEXDEFRAG for each of the indexes in the database.
--
-- EXAMPLES (optional) : EXEC isp_DBCC_INDEXDEFRAG @dbName = 'GT'
----------------------------------------------------------------------------------------------------
CREATE PROC isp_DBCC_INDEXDEFRAG
(@dbName SYSNAME)
AS
SET NOCOUNT ON
DECLARE @objName SYSNAME
DECLARE @idxName SYSNAME
DECLARE @SQL NVARCHAR(4000)
DECLARE @ID INT
DECLARE @RowCnt INT
CREATE TABLE ##Indexes
(
Indexes_ID INT IDENTITY(1, 1) NOT NULL,
IndexName SYSNAME NOT NULL,
ObjectName SYSNAME NOT NULL
)
SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName) '
SET @SQL = @SQL + 'SELECT i.name, o.name '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i '
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.id = o.id '
SET @SQL = @SQL + 'WHERE indid > 0 AND indid < 255 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND '
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0'
EXEC sp_executesql @statement = @SQL
SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName
FROM ##Indexes
ORDER BY Indexes_ID
SET @RowCnt = @@ROWCOUNT
WHILE @RowCnt <> 0
BEGIN
SET @SQL = 'DBCC INDEXDEFRAG(' + @dbname + ',' + @objName + ', ' + @idxName + ') WITH NO_INFOMSGS'
EXEC sp_executesql @statement = @SQL
SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName
FROM ##Indexes
WHERE Indexes_ID > @ID
ORDER BY Indexes_ID
SET @RowCnt = @@ROWCOUNT
END
DROP TABLE ##Indexes
RETURN 0
Here's a link to the old version.