Customized Update Stats on all active databases

Below is the script for Update Stats, which will extract the database information(Only active databases) and run the Update Stats one by one.

You can create a Stored Procedure in MSDB and schedule a job or dirctely you can run it.

declare @dbname sysname  --holds the name of the database
declare @stmt varchar(64)  --SQL Statement to be run
DECLARE @SQLVer int -- SQL Server primary version


set @dbname=@DB

IF @dbname is not NULL  
begin 
set @stmt='use '+'['+@dbname+']'+'exec sp_updatestats'
exec(@stmt)
return
end

SELECT @SQLVer = SUBSTRING(@@VERSION, CHARINDEX('-',@@VERSION)+1,2)
IF @SQLVer <> 9                      --Condition to check the version of SQL Server
begin
declare dbs cursor for 
select name from master..sysdatabases where 
status & (32 + 64 + 128 + 512 + 1024 + 2048 + 4096 + 32768) = 0  and name NOT IN ('master', 'msdb', 'model','tempdb')/*  To skip the databases which are
                                                                        32 = loading
                                                                        64 = pre recovery
                                                                        128 = recovering
                                                                        256 = not recovered
                                                                        512 = offline
                                                                        1024 = read only
                                                                        2048 = dbo use only
                                                                        4096 = single user
                                                                        32768 = emergency mode   */
end
else
begin
declare dbs cursor for 
select name from sys.databases where 
state  & (1 + 2 + 3 + 4 + 5  + 6 ) = 0 and is_read_only <>1 and   name NOT IN ('master', 'msdb', 'model','tempdb')  /*making use of the catalog view in SQL Server2005
                                                            and the state values indicate as below
                                                            Database state:
                                                            0 = ONLINE
                                                            1 = RESTORING
                                                            2 = RECOVERING
                                                            3 = RECOVERY_PENDING
                                                            4 = SUSPECT
                                                            5 = EMERGENCY
                                                            6 = OFFLINE            */

end

open dbs
fetch next from dbs into @dbname

while @@fetch_status=0
begin
print 'updatestats started on'+ '['+@dbname+']'
set @stmt='use '+'['+@dbname+']'+'exec sp_updatestats'
exec(@stmt)

fetch next from dbs into @dbname
end
close dbs
deallocate dbs