Update statistics in all database (sp_updatestats)

,

This script permit executes the sp_updatestats in all databases at same time.

First I create the stored procedure above in an admin database, for sample I create one database with ADMIN name where I put all administration objects create for me. You can create this sp in exist db, if you want.

Now you can execute this sp in Query Analyzer or create a job to automate this operation. To execute this, call the next commands:

use ADMIN --or the name of database where you create this sp
exec SPUpdateStats

Good work

CREATE PROCEDURE SPUpdateStats
AS

Set Nocount on
Declare db Cursor For
Select name from master.dbo.sysdatabases where name not in ('master','TempDB', 'msdb', 'model')

Declare @dbname varchar(60)
Declare @execmd nvarchar(150)

Open db
Fetch Next from db into @dbname
While @@Fetch_status=0
   begin
	if @dbname is null 
	  Begin
   	    Print 'null Value'
	  end
	else 
	  Begin
	    PRINT '###########################################################################'
            PRINT 'Update Statistics in ' + @dbname
            SELECT @execmd = 'USE ' + @dbname + ' EXEC sp_updatestats'
            EXEC(@execmd)
	    PRINT ''
          End
     Fetch Next from db into @dbname	
   end
Close db
Deallocate db
GO

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)