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. |