CREATE PROC isp_UPDATE_STATISTICS
(@dbName sysname, @sample int)
AS

SET NOCOUNT ON

DECLARE @SQL nvarchar(4000)
DECLARE @ID int
DECLARE @TableName sysname
DECLARE @RowCnt int

CREATE TABLE ##Tables
(
	TableID INT IDENTITY(1, 1) NOT NULL, 
	TableName SYSNAME NOT NULL
)

SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Tables (TableName) '
SET @SQL = @SQL + 'SELECT [name] '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysobjects ' 
SET @SQL = @SQL + 'WHERE xtype = ''U'' AND [name] <> ''dtproperties'''

EXEC sp_executesql @statement = @SQL

SELECT TOP 1 @ID = TableID, @TableName = TableName
FROM ##Tables
ORDER BY TableID

SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

	SET @SQL = 'UPDATE STATISTICS ' + @dbname + '.dbo.[' + @TableName + '] WITH SAMPLE ' + CONVERT(varchar(3), @sample) + ' PERCENT'

	EXEC sp_executesql @statement = @SQL

	SELECT TOP 1 @ID = TableID, @TableName = TableName
	FROM ##Tables
	WHERE TableID > @ID
	ORDER BY TableID

	SET @RowCnt = @@ROWCOUNT

END

DROP TABLE ##Tables


GO

quote:

---

Originally posted by sureshot

It seems like best practices to run CHECKDB every day, but I can't afford these constant timeouts caused by it. I think it would be a valid workaround to restore the backups to a separate box and run CHECKDB there, right?

---