SQL 2000: Reindexing a database

When the time comes where you need to manually reindex a database, the following script will allow you to do so for all tables in that database. Execute it using Query Analyzer, substituting the name of the database you'd like to reindex where "[your database name]" appears. The same script can be used with SQL 2005, although if you have aliases in place, they may interfere with successful script execution. (2005 specific scripts will be included elsewhere)

Reindexing in this fashion should not be done during production hours, as the db in question will be locked while the operation takes place.

USE [your database name]

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN PRINT 'Now reindexing ' + @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Once the operation is complete, you can review the output pane to see if errors occurred when reindexing the various tables in the db. Such errors will typically appear between the "Now reindexing" statement and the "DBCC Execution Completed" statement that corresponds to a given table.