Thursday, March 7, 2013

How to rebuild all index on all tables in a SQL Server database

This code will rebuild all indexes on all tables of the current SQL Server DB (without changing the fill factor)
 
Here is the T-SQL code:
DECLARE @tbName varchar(255)

DECLARE tbCursor CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_type = 'base table'

OPEN tbCursor

FETCH NEXT FROM tbCursor INTO @tbName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'rebuilding all indexes on ' + @tbName
        DBCC DBREINDEX (@tbName,' ',0) WITH NO_INFOMSGS 
        FETCH NEXT FROM tbCursor INTO @tbName
    END
CLOSE tbCursor

DEALLOCATE tbCursor

The source for this code is in the comments of this blog post
I just changed the code a bit, and color-coded it for easier reading  (using this highlighter)