Working with a few clients, I’ve noticed some issues with statistics in Sql Server 2005 not being updated frequently, even if auto stats is turned on at the database level and table level. I’ve noticed this often enough for me to cook up a quick script.

Initially I thought that sys.stats catalog view might have this information, but no. It can tell you if a particular stat is created automatically. So I decided to use the sp_autostats system stored procedure to get that ifmortion out. This should also work for Sql Server 2000.

SET NOCOUNT ON

DECLARE @TableName varchar(50)

PRINT ‘——– Stats Updates Report ——–‘

DECLARE Table_Cursor CURSOR fast_forward FOR
SELECT name
FROM sysobjects
WHERE xtype = ‘U’
ORDER BY name

OPEN Table_Cursor

FETCH NEXT FROM Table_Cursor
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

select @TableName

exec sp_autostats @TableName
— Get the next record.
FETCH NEXT FROM Table_Cursor
INTO @TableName
END

CLOSE Table_Cursor
DEALLOCATE Table_Cursor
GO