DBCC DBReindex

I ran into some ‘Message 823’ issues on my development DB, and upon running DBCC CheckDB() I saw that my indexes were the ones throwing the errors.
I got this handy cursor from SQL Server Performance, and decided to save it here for me, and for you, so that I don’t have to search for it over and over again. The script by the way is from the talented Brad McGehee, and I suggest checking out the article because it has more information and more cursors for failing indexes.

Without further ado:
USE DatabaseName –Enter the name of the database you want to reindex

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
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor


Conclusion: you, too, can fix your indexes with a cursor now.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.