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
--@DataINTEGER = 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 @indexINTEGER
DECLARE @sizeINTEGER
DECLARE @maxINTEGER
DECLARE @currentVARCHAR(300)
DECLARE @sqlVARCHAR(8000)
DECLARE @sql1VARCHAR(8000)
DECLARE @tempsqlVARCHAR(8000)

DECLARE @work TABLE
(tempidINTEGERIDENTITY(1,1)
,[name]VARCHAR(300))

CREATE TABLE #DB
(tempidINTEGERIDENTITY(1,1)
,[name]VARCHAR(300)
,[rows]VARCHAR(30)
,reservedVARCHAR(30)
,dataVARCHAR(30)
,index_sizeVARCHAR(30)
,unusedVARCHAR(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