DBCC REINDEX all user tables

,

This script reindexes all user and system indexes in the

database using the original fillfactor allocated to that

index. It can be executed from the Query Analyzer as stand alone SQL or you could wrap it in a stored procedure.

/* 

   This example rebuilds all indexes on 
   all user tables using the original fillfactor 
   value from the sysindexes table.
   
   David Wootton 11/01/2002
 
*/

drop table #tmp

declare @msg   varchar(500)

select sysobjects.name, sysobjects.id
into #tmp
from sysobjects
where sysobjects.type = 'U'
and sysobjects.name not like 'dt_proper%' 
order by sysobjects.name

declare reindexcursor cursor for

select #tmp.name,sysindexes.origfillfactor
from sysindexes, #tmp 
where sysindexes.id = (select #tmp.id from #tmp where #tmp.id=sysindexes.id)
group by #tmp.name,sysindexes.origfillfactor
having count(#tmp.name) >= 1
order by 1

open reindexcursor


declare @tname varchar(100)
declare @ffact varchar(10)

 
fetch next from  reindexcursor into @tname,@ffact

WHILE (@@FETCH_STATUS <> -1)

BEGIN

    IF (@@FETCH_STATUS <> -2)

    BEGIN  
	
	print ' '  
        SELECT @msg = 'DBCC DBREINDEX ('+(@tname ) +', '' '','+(@ffact)+')'
	print @msg
	exec (@msg) 
	print ' '
		
    END

    FETCH NEXT FROM  reindexcursor INTO @tname, @ffact

END

CLOSE  reindexcursor
DEALLOCATE  reindexcursor
GO

Rate

4.31 (13)

Share

Share

Rate

4.31 (13)