Tara Kizer Blog

Tara Kizer

DBCC DBREINDEX stored procedure

EDIT: This stored procedure has been updated.

The below stored procedure runs DBCC DBREINDEX for each of the indexes in the user database that is inputted into the sproc.  I recommend putting it into an Admin database (hey just name it Admin!) rather than in master.  Then just schedule isp_DBCC_DBREINDEX for each of the databases you want defragged.  Only run this routine when there is very little activity on your system as DBCC DBREINDEX will block queries from running. 

 

CREATE    PROC isp_DBCC_DBREINDEX
(@dbName SYSNAME)
AS
SET NOCOUNT ON

DECLARE @objID INT
DECLARE @objName SYSNAME
DECLARE @idxName SYSNAME
DECLARE @SQL NVARCHAR(4000)

SET @objID = 0

SET @SQL = ''
SET @SQL = @SQL + 'SELECT i.id, i.name, o.name AS ObjectName '
SET @SQL = @SQL + 'INTO ##Indexes '
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

WHILE @objID < (SELECT MAX(id) FROM ##Indexes)
BEGIN

 SELECT TOP 1 @objID = id, @idxName = name, @objName = ObjectName
 FROM ##Indexes
 WHERE id > @objID
 ORDER BY id

 SET @SQL = 'DBCC DBREINDEX(''' + @dbName + '.dbo.' + @objName + ''', ' + @idxName + ', 0) WITH NO_INFOMSGS'

 EXEC sp_executesql @statement = @SQL

END

DROP TABLE ##Indexes

RETURN 0

 

 

GO

Legacy Comments


Satish Balusa
2004-07-02
re: DBCC DBREINDEX stored procedure
Please be aware that the table will be Offline when DBCC REINDEX is working on it.

Tara
2004-07-02
re: DBCC DBREINDEX stored procedure
Yes that's why I mentioned that it should be run when there is very little activity on the system.