EDIT: fixed problems found by gam.

I have made yet another change to the DBCC DBREINDEX stored procedure. I modified it so that when a clustered index gets rebuilt, we don't rebuild the other indexes since that happens automatically for us. I made this change a little while ago, but I hadn't posted it yet. It's a good thing since I had to change it again. It turns out that only when a non-unique clustered index gets rebuilt, that the non-clustered ones get rebuilt as well. So when a unique clustered index gets rebuilt, we still need to rebuild the non-clustered ones. For more information, check this out. Hemanth Gorijala pointed this out to me in this thread.

Here's the new version:

----------------------------------------------------------------------------------------------------
-- OBJECT NAME         : isp_DBCC_DBREINDEX
--
-- AUTHOR               : Tara Duggan
-- DATE     : May 11, 2004
--
-- INPUTS    : @dbName - name of the database
-- OUTPUTS    : None
-- DEPENDENCIES         : None
--
-- DESCRIPTION         : This stored procedure runs DBCC DBREINDEX for each of the indexes in the database.
--
-- EXAMPLES (optional)  : EXEC isp_DBCC_DBREINDEX @dbName = 'GT'
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- 12/22/2004 - Tara Duggan
-- If table has a clustered index, just rebuild that index; otherwise rebuild all.
--
-- 12/30/2004 - Tara Duggan
-- If table has a non-unique clustered index, just rebuild that index; otherwise rebuild all.
----------------------------------------------------------------------------------------------------
ALTER             PROC isp_DBCC_DBREINDEX
(@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,
 Status INT NOT NULL
)

-- non-unique clustered indexes
SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName, Status) '
SET @SQL = @SQL + 'SELECT i.[name], o.[name], i.status '
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 i.indid = 1 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND (i.status & 2) = 0'

EXEC sp_executesql @statement = @SQL

-- non-clustered indexes except for those tables that have clustered non-unique indexes; these will be rebuilt automatically
SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName, Status) '
SET @SQL = @SQL + 'SELECT i.[name], o.[name], i.status '
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 i.indid > 1 AND i.indid < 255 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND '
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0 AND '
SET @SQL = @SQL + 'o.[name] NOT IN (SELECT ObjectName FROM ##Indexes)'

EXEC sp_executesql @statement = @SQL

-- unique clustered indexes
SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName, Status) '
SET @SQL = @SQL + 'SELECT i.[name], o.[name], i.status '
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 i.indid = 1 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND (i.status & 2) <> 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 DBREINDEX(''' + @dbName + '.dbo.' + @objName + ''', ' + @idxName + ', 0) 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

GO