This is a script that I use to quickly setup a daily job in SQL Server 2005 to update the statistics on all user databases.
It takes advantage of the enhanced functionality of procedure sp_updatestats in SQL Server 2005 to only update the stats when needed, so it has a fast runtime.
It uses of the VARCHAR(MAX) datatype to hold the entire command in one batch, so there is no need to open a cursor.
Edit: Added code to select only databases that are online, updateable, and not in single user mode.
/*
Update Statistics in All User Databases
Works in SQL Server 2005 and above
*/
declare @cmd nvarchar(max)
set @cmd = ''
-- Build command to update statistics
select @cmd = @cmd+
'
use '+quotename(a.name)+'
print ''*** Start Update Statistics for database ''+quotename(db_name())+
'' at ''+convert(varchar(30),getdate(),121)
exec sp_updatestats
print ''*** End Update Statistics for database ''+quotename(db_name())+
'' at ''+convert(varchar(30),getdate(),121)
'
from
(
select top 100 percent
aa.name
from
sys.sysdatabases aa
where
-- Exclude system database
-- Add more database names to exclude as needed.
name not in ('master','model','msdb','tempdb') and
-- Include only databases that are online
databasepropertyex(aa.name,'Status') = 'ONLINE' and
-- Include only databases that are updatable
databasepropertyex(aa.name,'Updateability') = 'READ_WRITE' and
-- Exclude databases in single user mode
databasepropertyex(aa.name,'UserAccess ') in ('RESTRICTED_USER','MULTI_USER')
order by
aa.name
) a
print '*** Start Update Statistics at '+convert(varchar(30),getdate(),121)
exec ( @cmd ) -- Execute Update Statistics commands
print '*** End Update Statistics at '+convert(varchar(30),getdate(),121)
CODO ERGO SUM
Update Statistics in All User Databases
Posted - 11/12/2008 : 12:09:56
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''