Search

Thursday, July 28, 2011

Cursor to run dbcc dbreindex against all tables

There seems to be a bug in ms_foreachtable. When we use that with dbcc dbreindex we have lately run in to the strange problem that the indexes are not reindexed at all. If we test with dbcc showcontig we can have very low numbers on scan density. To fix this i have writen this cursor. It takes the tables and order them by number of rows with the lowest number first. By using this we have succeeded to reindex Volvo.com etc.

declare @Tablename nvarchar(128)
declare @UserName nvarchar(128)
declare @string nvarchar(128)
declare @rows int
declare Dbreindex cursor for
select o.name, u.name from
sysindexes i, sysobjects o,sysusers U where
i.id = o.id and i.indid <= 1 and o.type = 'u'
and u.uid= o.uid order by i.rows asc
open Dbreindex
fetch next from Dbreindex
into @Tablename,@UserName
while @@fetch_status = 0
begin
set @string = 'dbcc dbreindex(''' +@UserName +'.'+ @Tablename + ''','''''+ ',70)'
exec( @string)
fetch next from Dbreindex
into @Tablename,@UserName
end
close Dbreindex
deallocate Dbreindex
go
exec sp_msforeachtable 'update statistics ? with fullscaN'
go
exec sp_msforeachtable 'sp_recompile "?"'

No comments:

Post a Comment