thecfguy

A Unique Developer

Rebuilds indexes for entire database

In some cases we may need to rebuild indexes for wholeover the database. There are many ways to rebuild indexes on table but one I like is using DBCC DBREINDEX statement. This one statement we reindex single or all indexes on table. But again this will be very time consuming when you want to rendex for all tables in database.

Hmm... Not to worry we can appy reindexing to all tables by below SQL code.

DECLARE @tablename varchar(200)DECLARE table_cursor CURSOR FOR select [name] from sysobjects where type='U'OPEN table_cursorFETCH NEXT FROM table_cursor INTO @tablenameWHILE @@FETCH_STATUS = 0BEGIN    DBCC DBREINDEX (@tablename, '', 80);    FETCH NEXT FROM table_cursor INTO @tablenameEND CLOSE table_cursorDEALLOCATE table_cursor

SQL statement 

select [name] from sysobjects where type='U' 
will get all tables name in database and just looping thourgh cursor will reindex in whole database.

Keep passion while running this query as it will take time to reindix all tabes.