EDIT: This stored procedure has been updated.
Earlier this month, I posted a stored procedure that runs DBCC DBREINDEX for each of the indexes in the user database that is inputted into the sproc. Well it turns out that the code was incorrect as it was only reindexing one index per table. Thanks mr_mist for finding this! This is an updated version of that sproc:
----------------------------------------------------------------------------------------------------
-- 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'
----------------------------------------------------------------------------------------------------
CREATE 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
)
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 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