<thecfguy blog="sharing thought">

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_cursor

FETCH NEXT FROM table_cursor INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC DBREINDEX (@tablename, '', 80);
    FETCH NEXT FROM table_cursor INTO @tablename
END 
CLOSE table_cursor
DEALLOCATE table_cursor

SQL statement 

select [name] from sysobjects where type='U' 

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