UPDATE STATISTICS job

-- Purpose: usp_UpdateStatisticSP
-- Author:  Rick Fonner
-- Date:    01 Jul 2004

IF EXISTS(SELECT name FROM sysobjects
		WHERE name = 'usp_UpdateStatisticSP' AND type = 'P')
BEGIN
	DROP PROCEDURE dbo.usp_UpdateStatisticSP
	PRINT 'Dropped dbo.usp_UpdateStatisticSP'
END
GO

CREATE PROCEDURE usp_UpdateStatisticSP
	--@Data	INTEGER = 10000
AS
BEGIN

	--#1  Remove msp_UpdateStatistics stored procedure if exists
	--#2  Declare and create local variables and tables
	--#3  Populate temp table with results from sp_MSforeachtable
	--#4  Loop through results of and insert selected items into working table
	--#5  Use the working table to populate string variables
	--#6  Create and execute procedure
	--#7  If required, alter and execute procedure

	SET NOCOUNT ON
   
	IF EXISTS	(
			SELECT
				*
			FROM
				dbo.[sysobjects]
			WHERE
				[id] = object_id(N'[dbo].[msp_UpdateStatistics]')
				and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	BEGIN
		DROP PROCEDURE [dbo].[msp_UpdateStatistics]
		PRINT 'drop procedure [dbo].[msp_UpdateStatistics]'
	END

	DECLARE @index		INTEGER
	DECLARE @size		INTEGER
	DECLARE @max		INTEGER
	DECLARE @current	VARCHAR(300)
	DECLARE @sql		VARCHAR(8000)
	DECLARE @sql1		VARCHAR(8000)
	DECLARE @tempsql	VARCHAR(8000)

	DECLARE @work TABLE
		(tempid		INTEGER	IDENTITY(1,1)
		,[name]		VARCHAR(300))

	CREATE TABLE #DB
		(tempid		INTEGER	IDENTITY(1,1)
		,[name]		VARCHAR(300)
		,[rows]		VARCHAR(30)
		,reserved		VARCHAR(30)
		,data		VARCHAR(30)
		,index_size	VARCHAR(30)
		,unused		VARCHAR(30))

	INSERT INTO #DB
		EXECUTE Northwind.dbo.[sp_MSforeachtable] @command1="sp_spaceused '?'"

	--SELECT * FROM #DB

	SELECT
		@index = 0
		,@max = max(tempid)
	FROM
		#DB

	WHILE @index < @max
	BEGIN

		SET @index = @index + 1 
		SET @current = ''

		SELECT
			@current = [name]
			,@size = convert(integer,Left(data,CHARINDEX(' ', data)))
		FROM
			#DB
		WHERE
			tempid = @index
			--AND CONVERT(INTEGER,LEFT(DATA,CHARINDEX(' ', data))) <= @Data
		
		IF @current <> ''
		BEGIN
			INSERT INTO @work
				([name])
			VALUES
				(@current)
		END

	END

	--SELECT * FROM @work

	SELECT
		@max		= MAX(tempid)
		,@index	= 0
		,@sql	= ''
		,@sql1	= ''
	FROM
		@work

	WHILE @index < @max
	BEGIN 

		SET @index = @index + 1 

		SELECT
			@current = [name]
		FROM
			@work
		WHERE
			tempid = @index

		IF LEN(@sql) < 7800 
		BEGIN
			SET @sql = @sql + 'UPDATE STATISTICS [' + @current + '];' + char(10)
			SET @sql = @sql + 'PRINT ''UPDATE STATISTICS [' + @current + '];''' + char(10)
		END
		ELSE IF LEN(@sql1) < 7800 
		BEGIN
			SET @sql1 = @sql1 + 'UPDATE STATISTICS [' + @current + '];' + char(10)
			SET @sql1 = @sql1 + 'PRINT ''UPDATE STATISTICS [' + @current + '];''' + char(10)
		END

	END

	DROP TABLE #DB

	--SELECT LEN(@sql)
	--SELECT LEN(@sql1)
	--PRINT @sql 
	--PRINT @sql1 

	SET @tempsql = 'CREATE PROCEDURE msp_UpdateStatistics as BEGIN ' + @sql + ' END'
	--SELECT @tempsql
	EXECUTE (@tempsql)
	EXECUTE msp_UpdateStatistics

	IF LEN(@sql1) <> 0
	BEGIN
		SET @tempsql = 'ALTER PROCEDURE msp_UpdateStatistics as BEGIN ' + @sql1 + ' END'
		--SELECT @tempsql
		EXECUTE (@tempsql)
		EXECUTE msp_UpdateStatistics
	END

	SET NOCOUNT OFF
END
GO

EXECUTE usp_UpdateStatisticSP
GO