Technical Article

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)

You rated this post out of 5. Change rating

Share

Share

Rate

4.5 (2)

You rated this post out of 5. Change rating