ReIndexing and Update Statistics

-- Database_StatisticUpdate 1

ALTER PROCEDURE Database_StatisticUpdate

(

@IsRecommandedUpdate BIT

)

AS

BEGIN

-- ReIndexing

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

IF(@IsRecommandedUpdate = 1)

BEGIN

-- Update Stats

EXEC sp_updatestats

END

ELSE

BEGIN

DECLARE @TableName VARCHAR(MAX)

SET @TableName = ''

DECLARE @CurrentTableName NVARCHAR(500)

SET @CurrentTableName = ''

DECLARE @sDelimiter VARCHAR(2)

SET @sDelimiter = ','

DECLARE @SqlQuery NVARCHAR(MAX)

SET @SqlQuery = ''

SELECT @TableName = COALESCE(@TableName+@sDelimiter ,'')+ name from sys.tables

where sys.tables.type = 'U'

SET @TableName = LTRIM(RTRIM(@TableName))

WHILE (CHARINDEX(@sDelimiter,@TableName,0) <> 0)

BEGIN

SET @CurrentTableName = ''

SELECT @CurrentTableName=RTRIM(LTRIM(SUBSTRING(@TableName,1,CHARINDEX(@sDelimiter,@TableName,0)-1))),

@TableName=RTRIM(LTRIM(SUBSTRING(@TableName,CHARINDEX(@sDelimiter,@TableName,0)+LEN(@sDelimiter),LEN(@TableName))))

SET @CurrentTableName = LTRIM(RTRIM(@CurrentTableName))

IF(@CurrentTableName <> '')

BEGIN

SET @SqlQuery = ''

SET @SqlQuery = 'UPDATE STATISTICS '+@CurrentTableName+ ' WITH FULLSCAN'

PRINT @SqlQuery

EXEC SP_EXECUTESQL @SqlQuery

END

END

END

END