Reindex all tables in all databases on sql server 2000 or sql server 20005

The first example is for sql server 2000. Normal code to get all databases and all tables. Runs also update statistics and recompile of stored procedures. The 0 in dbcc dbreindex means that the original fillfactor will be used. Change this to any number you like to use.( strongly recomended since full pages only is good to have on read only databases. 70-80 is often enough.) For sql server 2005 we are using alter index instead since this is the prefered way. You can edit the settings in the variable @Variables. Note that we are using online operatins here. This will not work if any of the columns is image, xml,ntext or text. Set it to off if you need to. To minimize the CPU power used for this operation it is possible to add e.g. maxdop=1. This will use max one CPU for the alter index job.

use master
go
declare @Databasename varchar(128)
declare @String varchar(1000)
declare Nonsysdatabases cursor for
select [name] from sysdatabases where dbid > 4 order by [name]
open Nonsysdatabases
set @String = '' + @@servername +''
fetch next from Nonsysdatabases
into @Databasename
while @@fetch_status = 0
begin
set @String = 'use [' + @Databasename + ']
declare @Tablename nvarchar(128)
declare @UserName nvarchar(128)
declare @string2 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 @string2 = ''dbcc dbreindex('''''' +@UserName +''.''+ @Tablename + '''''','''''''',0)''
exec( @string2)
fetch next from Dbreindex
into @Tablename,@UserName
end
close Dbreindex
deallocate Dbreindex
exec sp_msforeachtable ''update statistics ? with fullscaN''
exec sp_msforeachtable ''sp_recompile "?"''
'
exec (@String)
fetch next from Nonsysdatabases
into @Databasename
end
close Nonsysdatabases
deallocate Nonsysdatabases


-- This code is for sql server 20005. Edit the variable @Variables for the settings you need. Here we are using a fillfactor of 70% but you can use what ever you like.

use master
go
declare @Databasename varchar(128)
declare @String varchar(1000)
declare Nonsysdatabases cursor for
select [name] from sysdatabases where dbid > 4 order by [name]
open Nonsysdatabases
set @String = '' + @@servername +''
fetch next from Nonsysdatabases
into @Databasename
while @@fetch_status = 0
begin
set @String = 'use ' + @Databasename + '
declare @Tablename varchar(128)
declare @UserName varchar(128)
declare @string2 varchar(500)
declare @rows int
declare @Variables varchar (2000)
set @Variables=''
FILLFACTOR = 70,
ONLINE=ON ,
SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = OFF,
PAD_INDEX=on
''

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 @string2 = ''alter index all on '' +@UserName +''.[''+ @Tablename + ''] REBUILD WITH ('' +@variables +'' );''
exec( @string2)
fetch next from Dbreindex
into @Tablename,@UserName
end
close Dbreindex
deallocate Dbreindex
exec sp_msforeachtable ''update statistics ? with fullscaN''
exec sp_msforeachtable ''sp_recompile "?"''
'
exec (@String)
fetch next from Nonsysdatabases
into @Databasename
end
close Nonsysdatabases
deallocate Nonsysdatabases