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

Create a Comma Delimited List Using SELECT Clause From Table Column

USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
SELECT @listStr
GO

Update Statistics in database

select * from (

SELECT name
AS index_name,type, sys.indexes.type_desc,sys.indexes.object_id,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated,
datediff(day, STATS_DATE(OBJECT_ID, index_id) ,getdate()) AS DaysOld

FROM sys.indexes
WHERE OBJECT_ID in (SELECT sys.tables.object_id from sys.tables
where sys.tables.type = 'U')
) as t1
where DaysOld is not NULL --AND DaysOld <> 0
order by DaysOld desc


EXEC sp_updatestats (to update Statistic at Database level)