Thursday, 18 June 2009

Statistics Update - SQL SERVER

TO CREATE STATISTICS SCRIPT FOR ALL OUR REQUIRED DATABASES:

CREATE PROCEDURE SPUpdateStats
AS

Set Nocount on
Declare db Cursor For
Select distinct(Databasename) as Name FROM T_TableReindexing(nolock)
--Select name from master.dbo.sysdatabases where name in ('dbmonitor') and 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'
Return
end
else
Begin
PRINT '###########################################################################'
PRINT 'Update Statistics in ' + @dbname
SELECT @execmd = 'USE ' + @dbname + ' EXEC sp_updatestats'
Print (@execmd)
PRINT ''
End
Fetch Next from db into @dbname
end
Close db
Deallocate db


No comments:

Post a Comment