SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[IndexOptimize]

@Databases nvarchar(max),

@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLow nvarchar(max) = NULL,

@FragmentationLevel1 int = 5,

@FragmentationLevel2 int = 30,

@PageCountLevel int = 1000,

@SortInTempdb nvarchar(max) = 'N',

@MaxDOP int = NULL,

@FillFactor int = NULL,

@PadIndex nvarchar(max) = NULL,

@LOBCompaction nvarchar(max) = 'Y',

@UpdateStatistics nvarchar(max) = NULL,

@OnlyModifiedStatistics nvarchar(max) = 'N',

@StatisticsSample int = NULL,

@StatisticsResample nvarchar(max) = 'N',

@PartitionLevel nvarchar(max) = 'N',

@TimeLimit int = NULL,

@Indexes nvarchar(max) = NULL,

@Delay int = NULL,

@Execute nvarchar(max) = 'Y'

AS

BEGIN

----------------------------------------------------------------------------------------------------

--// Set options //--

----------------------------------------------------------------------------------------------------

SET NOCOUNT ON

SET LOCK_TIMEOUT 3600000

----------------------------------------------------------------------------------------------------

--// Declare variables //--

----------------------------------------------------------------------------------------------------

DECLARE @StartMessage nvarchar(max)

DECLARE @EndMessage nvarchar(max)

DECLARE @DatabaseMessage nvarchar(max)

DECLARE @ErrorMessage nvarchar(max)

DECLARE @Version numeric(18,10)

DECLARE @StartTime datetime

DECLARE @CurrentIndexList nvarchar(max)

DECLARE @CurrentIndexItem nvarchar(max)

DECLARE @CurrentIndexPosition int

DECLARE @CurrentID int

DECLARE @CurrentDatabase nvarchar(max)

DECLARE @CurrentIsDatabaseAccessible bit

DECLARE @CurrentMirroringRole nvarchar(max)

DECLARE @CurrentCommandSelect01 nvarchar(max)

DECLARE @CurrentCommandSelect02 nvarchar(max)

DECLARE @CurrentCommandSelect03 nvarchar(max)

DECLARE @CurrentCommandSelect04 nvarchar(max)

DECLARE @CurrentCommandSelect05 nvarchar(max)

DECLARE @CurrentCommandSelect06 nvarchar(max)

DECLARE @CurrentCommandSelect07 nvarchar(max)

DECLARE @CurrentCommandSelect08 nvarchar(max)

DECLARE @CurrentCommand01 nvarchar(max)

DECLARE @CurrentCommand02 nvarchar(max)

DECLARE @CurrentCommandOutput01 int

DECLARE @CurrentCommandOutput02 int

DECLARE @CurrentIxID int

DECLARE @CurrentSchemaID int

DECLARE @CurrentSchemaName nvarchar(max)

DECLARE @CurrentObjectID int

DECLARE @CurrentObjectName nvarchar(max)

DECLARE @CurrentObjectType nvarchar(max)

DECLARE @CurrentIndexID int

DECLARE @CurrentIndexName nvarchar(max)

DECLARE @CurrentIndexType int

DECLARE @CurrentStatisticsID int

DECLARE @CurrentStatisticsName nvarchar(max)

DECLARE @CurrentPartitionID bigint

DECLARE @CurrentPartitionNumber int

DECLARE @CurrentPartitionCount int

DECLARE @CurrentIsPartition bit

DECLARE @CurrentIndexExists bit

DECLARE @CurrentStatisticsExists bit

DECLARE @CurrentIsLOB bit

DECLARE @CurrentAllowPageLocks bit

DECLARE @CurrentNoRecompute bit

DECLARE @CurrentStatisticsModified bit

DECLARE @CurrentOnReadOnlyFileGroup bit

DECLARE @CurrentFragmentationLevel float

DECLARE @CurrentPageCount bigint

DECLARE @CurrentFragmentationGroup nvarchar(max)

DECLARE @CurrentAction nvarchar(max)

DECLARE @CurrentMaxDOP int

DECLARE @CurrentUpdateStatistics nvarchar(max)

DECLARE @CurrentComment nvarchar(max)

DECLARE @CurrentDelay datetime

DECLARE @tmpDatabases TABLE (ID int IDENTITY PRIMARY KEY,

DatabaseName nvarchar(max),

Completed bit)

DECLARE @tmpIndexesStatistics TABLE (IxID int IDENTITY PRIMARY KEY,

SchemaID int,

SchemaName nvarchar(max),

ObjectID int,

ObjectName nvarchar(max),

ObjectType nvarchar(max),

IndexID int,

IndexName nvarchar(max),

IndexType int,

StatisticsID int,

StatisticsName nvarchar(max),

PartitionID bigint,

PartitionNumber int,

PartitionCount int,

Selected bit,

Completed bit)

DECLARE @SelectedIndexes TABLE (DatabaseName nvarchar(max),

SchemaName nvarchar(max),

ObjectName nvarchar(max),

IndexName nvarchar(max),

Selected bit)

DECLARE @tmpIndexExists TABLE ([Count] int)

DECLARE @tmpStatisticsExists TABLE ([Count] int)

DECLARE @tmpIsLOB TABLE ([Count] int)

DECLARE @tmpAllowPageLocks TABLE ([Count] int)

DECLARE @tmpNoRecompute TABLE ([Count] int)

DECLARE @tmpStatisticsModified TABLE ([Count] int)

DECLARE @tmpOnReadOnlyFileGroup TABLE ([Count] int)

DECLARE @Actions TABLE ([Action] nvarchar(max))

INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_ONLINE')

INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_OFFLINE')

INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE')

DECLARE @ActionsPreferred TABLE (FragmentationGroup nvarchar(max),

Priority int,

[Action] nvarchar(max))

DECLARE @CurrentActionsAllowed TABLE ([Action] nvarchar(max))

DECLARE @Error int

SET @Error = 0

SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))

SET @CurrentDelay = DATEADD(ss,@Delay,'1900-01-01')

----------------------------------------------------------------------------------------------------

--// Log initial information //--

----------------------------------------------------------------------------------------------------

SET @StartTime = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),120),120)

SET @StartMessage = 'DateTime: ' + CONVERT(nvarchar,@StartTime,120) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(sys.schemas.name) FROM sys.schemas INNER JOIN sys.objects ON sys.schemas.[schema_id] = sys.objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)

SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @FragmentationHigh = ' + ISNULL('''' + REPLACE(@FragmentationHigh,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @FragmentationMedium = ' + ISNULL('''' + REPLACE(@FragmentationMedium,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @FragmentationLow = ' + ISNULL('''' + REPLACE(@FragmentationLow,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @FragmentationLevel1 = ' + ISNULL(CAST(@FragmentationLevel1 AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @FragmentationLevel2 = ' + ISNULL(CAST(@FragmentationLevel2 AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @PageCountLevel = ' + ISNULL(CAST(@PageCountLevel AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @SortInTempdb = ' + ISNULL('''' + REPLACE(@SortInTempdb,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @MaxDOP = ' + ISNULL(CAST(@MaxDOP AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @FillFactor = ' + ISNULL(CAST(@FillFactor AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @PadIndex = ' + ISNULL('''' + REPLACE(@PadIndex,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @LOBCompaction = ' + ISNULL('''' + REPLACE(@LOBCompaction,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @UpdateStatistics = ' + ISNULL('''' + REPLACE(@UpdateStatistics,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @OnlyModifiedStatistics = ' + ISNULL('''' + REPLACE(@OnlyModifiedStatistics,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @StatisticsSample = ' + ISNULL(CAST(@StatisticsSample AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @StatisticsResample = ' + ISNULL('''' + REPLACE(@StatisticsResample,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @PartitionLevel = ' + ISNULL('''' + REPLACE(@PartitionLevel,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @TimeLimit = ' + ISNULL(CAST(@TimeLimit AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @Indexes = ' + ISNULL('''' + REPLACE(@Indexes,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + ', @Delay = ' + ISNULL(CAST(@Delay AS nvarchar),'NULL')

SET @StartMessage = @StartMessage + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL')

SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10)

SET @StartMessage = REPLACE(@StartMessage,'%','%%')

RAISERROR(@StartMessage,10,1) WITH NOWAIT

----------------------------------------------------------------------------------------------------

--// Select databases //--

----------------------------------------------------------------------------------------------------

IF @Databases IS NULL OR @Databases = ''

BEGIN

SET @ErrorMessage = 'The value for parameter @Databases is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

INSERT INTO @tmpDatabases (DatabaseName, Completed)

SELECT DatabaseName AS DatabaseName,

0 AS Completed

FROM dbo.DatabaseSelect (@Databases)

ORDER BY DatabaseName ASC

IF @@ERROR <> 0 OR (@@ROWCOUNT = 0 AND @Databases <> 'USER_DATABASES')

BEGIN

SET @ErrorMessage = 'Error selecting databases.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

----------------------------------------------------------------------------------------------------

--// Select indexes //--

----------------------------------------------------------------------------------------------------

SET @CurrentIndexList = @Indexes

SET @CurrentIndexList = REPLACE(REPLACE(@CurrentIndexList,'''',''),'"','')

WHILE CHARINDEX(',,',@CurrentIndexList) > 0 SET @CurrentIndexList = REPLACE(@CurrentIndexList,',,',',')

WHILE CHARINDEX(', ',@CurrentIndexList) > 0 SET @CurrentIndexList = REPLACE(@CurrentIndexList,', ',',')

WHILE CHARINDEX(' ,',@CurrentIndexList) > 0 SET @CurrentIndexList = REPLACE(@CurrentIndexList,' ,',',')

IF RIGHT(@CurrentIndexList,1) = ',' SET @CurrentIndexList = LEFT(@CurrentIndexList,LEN(@CurrentIndexList) - 1)

IF LEFT(@CurrentIndexList,1) = ',' SET @CurrentIndexList = RIGHT(@CurrentIndexList,LEN(@CurrentIndexList) - 1)

SET @CurrentIndexList = LTRIM(RTRIM(@CurrentIndexList))

WHILE LEN(@CurrentIndexList) > 0

BEGIN

SET @CurrentIndexPosition = CHARINDEX(',', @CurrentIndexList)

IF @CurrentIndexPosition = 0

BEGIN

SET @CurrentIndexItem = @CurrentIndexList

SET @CurrentIndexList = ''

END

ELSE

BEGIN

SET @CurrentIndexItem = LEFT(@CurrentIndexList, @CurrentIndexPosition - 1)

SET @CurrentIndexList = RIGHT(@CurrentIndexList, LEN(@CurrentIndexList) - @CurrentIndexPosition)

END;

WITH IndexItem01 (IndexItem, Selected) AS (

SELECT CASE WHEN @CurrentIndexItem LIKE '-%' THEN RIGHT(@CurrentIndexItem,LEN(@CurrentIndexItem) - 1) ELSE @CurrentIndexItem END AS IndexItem,

CASE WHEN @CurrentIndexItem LIKE '-%' THEN 0 ELSE 1 END AS Selected),

IndexItem02 (IndexItem, Selected) AS (

SELECT CASE WHEN IndexItem = 'ALL_INDEXES' THEN '%.%.%.%' ELSE IndexItem END AS IndexItem,

Selected

FROM IndexItem01)

INSERT INTO @SelectedIndexes (DatabaseName, SchemaName, ObjectName, IndexName, Selected)

SELECT DatabaseName = CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,3) ELSE PARSENAME(IndexItem,4) END,

SchemaName = CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,2) ELSE PARSENAME(IndexItem,3) END,

ObjectName = CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,1) ELSE PARSENAME(IndexItem,2) END,

IndexName = CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN '%' ELSE PARSENAME(IndexItem,1) END,

Selected

FROM IndexItem02

END

IF EXISTS(SELECT * FROM @SelectedIndexes WHERE DatabaseName IS NULL OR SchemaName IS NULL OR ObjectName IS NULL OR IndexName IS NULL) OR (@Indexes IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedIndexes))

BEGIN

SET @ErrorMessage = 'Error selecting indexes.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END;

----------------------------------------------------------------------------------------------------

--// Select actions //--

----------------------------------------------------------------------------------------------------

WITH FragmentationHigh AS

(

SELECT CASE WHEN CHARINDEX(',', @FragmentationHigh) = 0 THEN @FragmentationHigh ELSE SUBSTRING(@FragmentationHigh, 1, CHARINDEX(',', @FragmentationHigh) - 1) END AS [Action],

CASE WHEN CHARINDEX(',', @FragmentationHigh) = 0 THEN '' ELSE SUBSTRING(@FragmentationHigh, CHARINDEX(',', @FragmentationHigh) + 1, LEN(@FragmentationHigh)) END AS String,

1 AS Priority,

CASE WHEN CHARINDEX(',', @FragmentationHigh) = 0 THEN 0 ELSE 1 END [Continue]

WHERE @FragmentationHigh IS NOT NULL

UNION ALL

SELECT CASE WHEN CHARINDEX(',', String) = 0 THEN String ELSE SUBSTRING(String, 1, CHARINDEX(',', String) - 1) END AS [Action],

CASE WHEN CHARINDEX(',', String) = 0 THEN '' ELSE SUBSTRING(String, CHARINDEX(',', String) + 1, LEN(String)) END AS String,

Priority + 1 AS Priority,

CASE WHEN CHARINDEX(',', String) = 0 THEN 0 ELSE 1 END [Continue]

FROM FragmentationHigh

WHERE [Continue] = 1

),

FragmentationMedium AS

(

SELECT CASE WHEN CHARINDEX(',', @FragmentationMedium) = 0 THEN @FragmentationMedium ELSE SUBSTRING(@FragmentationMedium, 1, CHARINDEX(',', @FragmentationMedium) - 1) END AS [Action],

CASE WHEN CHARINDEX(',', @FragmentationMedium) = 0 THEN '' ELSE SUBSTRING(@FragmentationMedium, CHARINDEX(',', @FragmentationMedium) + 1, LEN(@FragmentationMedium)) END AS String,

1 AS Priority,

CASE WHEN CHARINDEX(',', @FragmentationMedium) = 0 THEN 0 ELSE 1 END [Continue]

WHERE @FragmentationMedium IS NOT NULL

UNION ALL

SELECT CASE WHEN CHARINDEX(',', String) = 0 THEN String ELSE SUBSTRING(String, 1, CHARINDEX(',', String) - 1) END AS [Action],

CASE WHEN CHARINDEX(',', String) = 0 THEN '' ELSE SUBSTRING(String, CHARINDEX(',', String) + 1, LEN(String)) END AS String,

Priority + 1 AS Priority,

CASE WHEN CHARINDEX(',', String) = 0 THEN 0 ELSE 1 END [Continue]

FROM FragmentationMedium

WHERE [Continue] = 1

),

FragmentationLow AS

(

SELECT CASE WHEN CHARINDEX(',', @FragmentationLow) = 0 THEN @FragmentationLow ELSE SUBSTRING(@FragmentationLow, 1, CHARINDEX(',', @FragmentationLow) - 1) END AS [Action],

CASE WHEN CHARINDEX(',', @FragmentationLow) = 0 THEN '' ELSE SUBSTRING(@FragmentationLow, CHARINDEX(',', @FragmentationLow) + 1, LEN(@FragmentationLow)) END AS String,

1 AS Priority,

CASE WHEN CHARINDEX(',', @FragmentationLow) = 0 THEN 0 ELSE 1 END [Continue]

WHERE @FragmentationLow IS NOT NULL

UNION ALL

SELECT CASE WHEN CHARINDEX(',', String) = 0 THEN String ELSE SUBSTRING(String, 1, CHARINDEX(',', String) - 1) END AS [Action],

CASE WHEN CHARINDEX(',', String) = 0 THEN '' ELSE SUBSTRING(String, CHARINDEX(',', String) + 1, LEN(String)) END AS String,

Priority + 1 AS Priority,

CASE WHEN CHARINDEX(',', String) = 0 THEN 0 ELSE 1 END [Continue]

FROM FragmentationLow

WHERE [Continue] = 1

)

INSERT INTO @ActionsPreferred(FragmentationGroup, Priority, [Action])

SELECT 'High' AS FragmentationGroup, Priority, [Action]

FROM FragmentationHigh

UNION

SELECT 'Medium' AS FragmentationGroup, Priority, [Action]

FROM FragmentationMedium

UNION

SELECT 'Low' AS FragmentationGroup, Priority, [Action]

FROM FragmentationLow

----------------------------------------------------------------------------------------------------

--// Check input parameters //--

----------------------------------------------------------------------------------------------------

IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'High' AND [Action] NOT IN(SELECT * FROM @Actions))

OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'High' GROUP BY [Action] HAVING COUNT(*) > 1)

BEGIN

SET @ErrorMessage = 'The value for parameter @FragmentationHigh is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'Medium' AND [Action] NOT IN(SELECT * FROM @Actions))

OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'Medium' GROUP BY [Action] HAVING COUNT(*) > 1)

BEGIN

SET @ErrorMessage = 'The value for parameter @FragmentationMedium is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'Low' AND [Action] NOT IN(SELECT * FROM @Actions))

OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'Low' GROUP BY [Action] HAVING COUNT(*) > 1)

BEGIN

SET @ErrorMessage = 'The value for parameter @FragmentationLow is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @FragmentationLevel1 <= 0 OR @FragmentationLevel1 >= 100 OR @FragmentationLevel1 >= @FragmentationLevel2 OR @FragmentationLevel1 IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @FragmentationLevel1 is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @FragmentationLevel2 <= 0 OR @FragmentationLevel2 >= 100 OR @FragmentationLevel2 <= @FragmentationLevel1 OR @FragmentationLevel2 IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @FragmentationLevel2 is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @PageCountLevel < 0 OR @PageCountLevel IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @PageCountLevel is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @SortInTempdb NOT IN('Y','N') OR @SortInTempdb IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @SortInTempdb is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @MaxDOP < 0 OR @MaxDOP > 64 OR @MaxDOP > (SELECT cpu_count FROM sys.dm_os_sys_info) OR (@MaxDOP > 1 AND SERVERPROPERTY('EngineEdition') <> 3)

BEGIN

SET @ErrorMessage = 'The value for parameter @MaxDOP is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @MaxDOP > 1 AND SERVERPROPERTY('EngineEdition') <> 3

BEGIN

SET @ErrorMessage = 'Parallel index operations are only supported in Enterprise, Developer and Datacenter Edition.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @FillFactor <= 0 OR @FillFactor > 100

BEGIN

SET @ErrorMessage = 'The value for parameter @FillFactor is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @PadIndex NOT IN('Y','N')

BEGIN

SET @ErrorMessage = 'The value for parameter @PadIndex is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @LOBCompaction NOT IN('Y','N') OR @LOBCompaction IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @LOBCompaction is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @UpdateStatistics NOT IN('ALL','COLUMNS','INDEX')

BEGIN

SET @ErrorMessage = 'The value for parameter @UpdateStatistics is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @OnlyModifiedStatistics NOT IN('Y','N') OR @OnlyModifiedStatistics IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @OnlyModifiedStatistics is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @StatisticsSample <= 0 OR @StatisticsSample > 100

BEGIN

SET @ErrorMessage = 'The value for parameter @StatisticsSample is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @StatisticsResample NOT IN('Y','N') OR @StatisticsResample IS NULL OR (@StatisticsResample = 'Y' AND @StatisticsSample IS NOT NULL)

BEGIN

SET @ErrorMessage = 'The value for parameter @StatisticsResample is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @PartitionLevel NOT IN('Y','N') OR @PartitionLevel IS NULL OR (@PartitionLevel = 'Y' AND SERVERPROPERTY('EngineEdition') <> 3)

BEGIN

SET @ErrorMessage = 'The value for parameter @PartitionLevel is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @PartitionLevel = 'Y' AND SERVERPROPERTY('EngineEdition') <> 3

BEGIN

SET @ErrorMessage = 'Table partitioning is only supported in Enterprise, Developer and Datacenter Edition.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @TimeLimit < 0

BEGIN

SET @ErrorMessage = 'The value for parameter @TimeLimit is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Delay < 0

BEGIN

SET @ErrorMessage = 'The value for parameter @Delay is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

IF @Execute NOT IN('Y','N') OR @Execute IS NULL

BEGIN

SET @ErrorMessage = 'The value for parameter @Execute is not supported.' + CHAR(13) + CHAR(10)

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

SET @Error = @@ERROR

END

----------------------------------------------------------------------------------------------------

--// Check error variable //--

----------------------------------------------------------------------------------------------------

IF @Error <> 0 GOTO Logging

----------------------------------------------------------------------------------------------------

--// Execute commands //--

----------------------------------------------------------------------------------------------------

WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)

BEGIN

SELECT TOP 1 @CurrentID = ID,

@CurrentDatabase = DatabaseName

FROM @tmpDatabases

WHERE Completed = 0

ORDER BY ID ASC

IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = DB_ID(@CurrentDatabase) AND database_guid IS NOT NULL)

BEGIN

SET @CurrentIsDatabaseAccessible = 1

END

ELSE

BEGIN

SET @CurrentIsDatabaseAccessible = 0

END

SELECT @CurrentMirroringRole = mirroring_role_desc

FROM sys.database_mirroring

WHERE database_id = DB_ID(@CurrentDatabase)

-- Set database message

SET @DatabaseMessage = 'DateTime: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabase) + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'Status') AS nvarchar) + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Mirroring role: ' + ISNULL(@CurrentMirroringRole,'None') + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Standby: ' + CASE WHEN DATABASEPROPERTYEX(@CurrentDatabase,'IsInStandBy') = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Updateability: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'Updateability') AS nvarchar) + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'User access: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'UserAccess') AS nvarchar) + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)

SET @DatabaseMessage = @DatabaseMessage + 'Recovery model: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'Recovery') AS nvarchar) + CHAR(13) + CHAR(10)

SET @DatabaseMessage = REPLACE(@DatabaseMessage,'%','%%')

RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT

IF DATABASEPROPERTYEX(@CurrentDatabase,'Status') = 'ONLINE'

AND NOT (DATABASEPROPERTYEX(@CurrentDatabase,'UserAccess') = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)

AND DATABASEPROPERTYEX(@CurrentDatabase,'Updateability') = 'READ_WRITE'

BEGIN

-- Select indexes in the current database

SET @CurrentCommandSelect01 = 'SELECT SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IndexID, IndexName, IndexType, StatisticsID, StatisticsName, PartitionID, PartitionNumber, PartitionCount, Selected, Completed FROM (SELECT ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] AS SchemaID, ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[name] AS SchemaName, ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] AS ObjectID, ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[name] AS ObjectName, RTRIM(' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type]) AS ObjectType, ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id AS IndexID, ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[name] AS IndexName, ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type] AS IndexType, ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.stats_id AS StatisticsID, ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.name AS StatisticsName'

IF @PartitionLevel = 'Y' SET @CurrentCommandSelect01 = @CurrentCommandSelect01 + ', ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.partition_id AS PartitionID, ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.partition_number AS PartitionNumber, IndexPartitions.partition_count AS PartitionCount'

IF @PartitionLevel = 'N' SET @CurrentCommandSelect01 = @CurrentCommandSelect01 + ', NULL AS PartitionID, NULL AS PartitionNumber, NULL AS PartitionCount'

SET @CurrentCommandSelect01 = @CurrentCommandSelect01 + ', 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[schema_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.stats ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.[object_id] AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[index_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.[stats_id]'

IF @PartitionLevel = 'Y' SET @CurrentCommandSelect01 = @CurrentCommandSelect01 + ' LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.[object_id] AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.index_id LEFT OUTER JOIN (SELECT [object_id], index_id, COUNT(*) AS partition_count FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions GROUP BY [object_id], index_id) IndexPartitions ON ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.[object_id] = IndexPartitions.[object_id] AND ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.[index_id] = IndexPartitions.[index_id]'

SET @CurrentCommandSelect01 = @CurrentCommandSelect01 + ' WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] IN(''U'',''V'') AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.is_ms_shipped = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type] IN(1,2,3,4) AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_disabled = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_hypothetical = 0'

IF @UpdateStatistics IN('ALL','COLUMNS') SET @CurrentCommandSelect01 = @CurrentCommandSelect01 + ' UNION SELECT ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] AS SchemaID, ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[name] AS SchemaName, ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] AS ObjectID, ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[name] AS ObjectName, RTRIM(' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type]) AS ObjectType, NULL AS IndexID, NULL AS IndexName, NULL AS IndexType, ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.stats_id AS StatisticsID, ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.name AS StatisticsName, NULL AS PartitionID, NULL AS PartitionNumber, NULL AS PartitionCount, 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.stats INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[schema_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] IN(''U'',''V'') AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.is_ms_shipped = 0 AND NOT EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.[object_id] AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.stats_id)'

SET @CurrentCommandSelect01 = @CurrentCommandSelect01 + ') IndexesStatistics ORDER BY SchemaName ASC, ObjectName ASC, CASE WHEN IndexType IS NULL THEN 1 ELSE 0 END ASC, IndexType ASC, IndexName ASC, StatisticsName ASC, PartitionNumber ASC'

INSERT INTO @tmpIndexesStatistics (SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IndexID, IndexName, IndexType, StatisticsID, StatisticsName, PartitionID, PartitionNumber, PartitionCount, Selected, Completed)

EXECUTE(@CurrentCommandSelect01)

SET @Error = @@ERROR

IF @Error = 1222

BEGIN

SET @ErrorMessage = 'The system tables are locked in the database ' + QUOTENAME(@CurrentDatabase) + '.' + CHAR(13) + CHAR(10)

SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

END

IF @Indexes IS NULL

BEGIN

UPDATE tmpIndexesStatistics

SET tmpIndexesStatistics.Selected = 1

FROM @tmpIndexesStatistics tmpIndexesStatistics

END

ELSE

BEGIN

UPDATE tmpIndexesStatistics

SET tmpIndexesStatistics.Selected = SelectedIndexes.Selected

FROM @tmpIndexesStatistics tmpIndexesStatistics

INNER JOIN @SelectedIndexes SelectedIndexes

ON @CurrentDatabase LIKE REPLACE(SelectedIndexes.DatabaseName,'_','[_]') AND tmpIndexesStatistics.SchemaName LIKE REPLACE(SelectedIndexes.SchemaName,'_','[_]') AND tmpIndexesStatistics.ObjectName LIKE REPLACE(SelectedIndexes.ObjectName,'_','[_]') AND COALESCE(tmpIndexesStatistics.IndexName,tmpIndexesStatistics.StatisticsName) LIKE REPLACE(SelectedIndexes.IndexName,'_','[_]')

WHERE SelectedIndexes.Selected = 1

UPDATE tmpIndexesStatistics

SET tmpIndexesStatistics.Selected = SelectedIndexes.Selected

FROM @tmpIndexesStatistics tmpIndexesStatistics

INNER JOIN @SelectedIndexes SelectedIndexes

ON @CurrentDatabase LIKE REPLACE(SelectedIndexes.DatabaseName,'_','[_]') AND tmpIndexesStatistics.SchemaName LIKE REPLACE(SelectedIndexes.SchemaName,'_','[_]') AND tmpIndexesStatistics.ObjectName LIKE REPLACE(SelectedIndexes.ObjectName,'_','[_]') AND COALESCE(tmpIndexesStatistics.IndexName,tmpIndexesStatistics.StatisticsName) LIKE REPLACE(SelectedIndexes.IndexName,'_','[_]')

WHERE SelectedIndexes.Selected = 0

END

WHILE EXISTS (SELECT * FROM @tmpIndexesStatistics WHERE Selected = 1 AND Completed = 0)

BEGIN

SELECT TOP 1 @CurrentIxID = IxID,

@CurrentSchemaID = SchemaID,

@CurrentSchemaName = SchemaName,

@CurrentObjectID = ObjectID,

@CurrentObjectName = ObjectName,

@CurrentObjectType = ObjectType,

@CurrentIndexID = IndexID,

@CurrentIndexName = IndexName,

@CurrentIndexType = IndexType,

@CurrentStatisticsID = StatisticsID,

@CurrentStatisticsName = StatisticsName,

@CurrentPartitionID = PartitionID,

@CurrentPartitionNumber = PartitionNumber,

@CurrentPartitionCount = PartitionCount

FROM @tmpIndexesStatistics

WHERE Selected = 1

AND Completed = 0

ORDER BY IxID ASC

-- Is the index a partition?

IF @CurrentPartitionNumber IS NULL OR @CurrentPartitionCount = 1 BEGIN SET @CurrentIsPartition = 0 END ELSE BEGIN SET @CurrentIsPartition = 1 END

-- Does the index exist?

IF @CurrentIndexID IS NOT NULL

BEGIN

IF @CurrentIsPartition = 0 SET @CurrentCommandSelect02 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[schema_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] IN(''U'',''V'') AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.is_ms_shipped = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type] IN(1,2,3,4) AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_disabled = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_hypothetical = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] = ' + CAST(@CurrentSchemaID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[name] = N' + QUOTENAME(@CurrentSchemaName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[name] = N' + QUOTENAME(@CurrentObjectName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] = N' + QUOTENAME(@CurrentObjectType,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id = ' + CAST(@CurrentIndexID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[name] = N' + QUOTENAME(@CurrentIndexName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type] = ' + CAST(@CurrentIndexType AS nvarchar)

IF @CurrentIsPartition = 1 SET @CurrentCommandSelect02 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[schema_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.[object_id] AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.index_id WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] IN(''U'',''V'') AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.is_ms_shipped = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type] IN(1,2,3,4) AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_disabled = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.is_hypothetical = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] = ' + CAST(@CurrentSchemaID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[name] = N' + QUOTENAME(@CurrentSchemaName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[name] = N' + QUOTENAME(@CurrentObjectName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] = N' + QUOTENAME(@CurrentObjectType,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.index_id = ' + CAST(@CurrentIndexID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[name] = N' + QUOTENAME(@CurrentIndexName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[type] = ' + CAST(@CurrentIndexType AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.partition_id = ' + CAST(@CurrentPartitionID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.partitions.partition_number = ' + CAST(@CurrentPartitionNumber AS nvarchar)

INSERT INTO @tmpIndexExists ([Count])

EXECUTE(@CurrentCommandSelect02)

IF (SELECT [Count] FROM @tmpIndexExists) > 0 BEGIN SET @CurrentIndexExists = 1 END ELSE BEGIN SET @CurrentIndexExists = 0 END

IF @CurrentIndexExists = 0 GOTO NoAction

END

-- Does the statistics exist?

IF @CurrentStatisticsID IS NOT NULL

BEGIN

SET @CurrentCommandSelect06 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.stats INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[schema_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] IN(''U'',''V'') AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.is_ms_shipped = 0 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[schema_id] = ' + CAST(@CurrentSchemaID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas.[name] = N' + QUOTENAME(@CurrentSchemaName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[name] = N' + QUOTENAME(@CurrentObjectName,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.objects.[type] = N' + QUOTENAME(@CurrentObjectType,'''') + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.stats_id = ' + CAST(@CurrentStatisticsID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.[name] = N' + QUOTENAME(@CurrentStatisticsName,'''')

INSERT INTO @tmpStatisticsExists ([Count])

EXECUTE(@CurrentCommandSelect06)

IF (SELECT [Count] FROM @tmpStatisticsExists) > 0 BEGIN SET @CurrentStatisticsExists = 1 END ELSE BEGIN SET @CurrentStatisticsExists = 0 END

IF @CurrentStatisticsExists = 0 GOTO NoAction

END

-- Does the index contain a LOB?

IF @CurrentIndexID IS NOT NULL AND @CurrentIndexType IN(1,2)

BEGIN

IF @CurrentIndexType = 1 SET @CurrentCommandSelect03 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.columns INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.types ON ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.system_type_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.types.user_type_id OR (' + QUOTENAME(@CurrentDatabase) + '.sys.columns.user_type_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.types.user_type_id AND '+ QUOTENAME(@CurrentDatabase) + '.sys.types.is_assembly_type = 1) WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND (' + QUOTENAME(@CurrentDatabase) + '.sys.types.name IN(''xml'',''image'',''text'',''ntext'') OR (' + QUOTENAME(@CurrentDatabase) + '.sys.types.name IN(''varchar'',''nvarchar'',''varbinary'') AND ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.max_length = -1) OR (' + QUOTENAME(@CurrentDatabase) + '.sys.types.is_assembly_type = 1 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.max_length = -1))'

IF @CurrentIndexType = 2 SET @CurrentCommandSelect03 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.columns ON ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns.[object_id] = ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.[object_id] AND ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns.column_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.column_id INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.types ON ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.system_type_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.types.user_type_id OR (' + QUOTENAME(@CurrentDatabase) + '.sys.columns.user_type_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.types.user_type_id AND ' + QUOTENAME(@CurrentDatabase) + '.sys.types.is_assembly_type = 1) WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns.index_id = ' + CAST(@CurrentIndexID AS nvarchar) + ' AND (' + QUOTENAME(@CurrentDatabase) + '.sys.types.[name] IN(''xml'',''image'',''text'',''ntext'') OR (' + QUOTENAME(@CurrentDatabase) + '.sys.types.[name] IN(''varchar'',''nvarchar'',''varbinary'') AND ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.max_length = -1) OR (' + QUOTENAME(@CurrentDatabase) + '.sys.types.is_assembly_type = 1 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.columns.max_length = -1))'

INSERT INTO @tmpIsLOB ([Count])

EXECUTE(@CurrentCommandSelect03)

IF (SELECT [Count] FROM @tmpIsLOB) > 0 BEGIN SET @CurrentIsLOB = 1 END ELSE BEGIN SET @CurrentIsLOB = 0 END

END

-- Is Allow_Page_Locks set to On?

IF @CurrentIndexID IS NOT NULL

BEGIN

SET @CurrentCommandSelect04 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[index_id] = ' + CAST(@CurrentIndexID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[allow_page_locks] = 1'

INSERT INTO @tmpAllowPageLocks ([Count])

EXECUTE(@CurrentCommandSelect04)

IF (SELECT [Count] FROM @tmpAllowPageLocks) > 0 BEGIN SET @CurrentAllowPageLocks = 1 END ELSE BEGIN SET @CurrentAllowPageLocks = 0 END

END

-- Is No_Recompute set to On?

IF @CurrentStatisticsID IS NOT NULL

BEGIN

SET @CurrentCommandSelect07 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.stats WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.[stats_id] = ' + CAST(@CurrentStatisticsID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.stats.[no_recompute] = 1'

INSERT INTO @tmpNoRecompute ([Count])

EXECUTE(@CurrentCommandSelect07)

IF (SELECT [Count] FROM @tmpNoRecompute) > 0 BEGIN SET @CurrentNoRecompute = 1 END ELSE BEGIN SET @CurrentNoRecompute = 0 END

END

-- Has the data in the statistics been modified since the statistics was last updated?

IF @CurrentStatisticsID IS NOT NULL

BEGIN

SET @CurrentCommandSelect08 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.sysindexes WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.sysindexes.[id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.sysindexes.[indid] = ' + CAST(@CurrentStatisticsID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.sysindexes.[rowmodctr] <> 0'

INSERT INTO @tmpStatisticsModified ([Count])

EXECUTE(@CurrentCommandSelect08)

IF (SELECT [Count] FROM @tmpStatisticsModified) > 0 BEGIN SET @CurrentStatisticsModified = 1 END ELSE BEGIN SET @CurrentStatisticsModified = 0 END

END

-- Is the index on a read-only filegroup?

IF @CurrentIndexID IS NOT NULL

BEGIN

SET @CurrentCommandSelect05 = 'SELECT COUNT(*) FROM (SELECT ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.destination_data_spaces ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.data_space_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.destination_data_spaces.partition_scheme_id INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups ON ' + QUOTENAME(@CurrentDatabase) + '.sys.destination_data_spaces.data_space_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.data_space_id WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.is_read_only = 1 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[index_id] = ' + CAST(@CurrentIndexID AS nvarchar)

IF @CurrentIsPartition = 1 SET @CurrentCommandSelect05 = @CurrentCommandSelect05 + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.destination_data_spaces.destination_id = ' + CAST(@CurrentPartitionNumber AS nvarchar)

SET @CurrentCommandSelect05 = @CurrentCommandSelect05 + ' UNION SELECT ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups ON ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.data_space_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.data_space_id WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.is_read_only = 1 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar) + ' AND ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes.[index_id] = ' + CAST(@CurrentIndexID AS nvarchar)

IF @CurrentIndexType = 1 SET @CurrentCommandSelect05 = @CurrentCommandSelect05 + ' UNION SELECT ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.tables INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups ON ' + QUOTENAME(@CurrentDatabase) + '.sys.tables.lob_data_space_id = ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.data_space_id WHERE ' + QUOTENAME(@CurrentDatabase) + '.sys.filegroups.is_read_only = 1 AND ' + QUOTENAME(@CurrentDatabase) + '.sys.tables.[object_id] = ' + CAST(@CurrentObjectID AS nvarchar)

SET @CurrentCommandSelect05 = @CurrentCommandSelect05 + ') ReadOnlyFileGroups'

INSERT INTO @tmpOnReadOnlyFileGroup ([Count])

EXECUTE(@CurrentCommandSelect05)

IF (SELECT [Count] FROM @tmpOnReadOnlyFileGroup) > 0 BEGIN SET @CurrentOnReadOnlyFileGroup = 1 END ELSE BEGIN SET @CurrentOnReadOnlyFileGroup = 0 END

END

-- Is the index fragmented?

IF @CurrentIndexID IS NOT NULL

AND EXISTS(SELECT * FROM @ActionsPreferred)

AND (EXISTS(SELECT Priority, [Action], COUNT(*) FROM @ActionsPreferred GROUP BY Priority, [Action] HAVING COUNT(*) <> 3) OR @PageCountLevel > 0)

BEGIN

SELECT @CurrentFragmentationLevel = MAX(avg_fragmentation_in_percent),

@CurrentPageCount = SUM(page_count)

FROM sys.dm_db_index_physical_stats(DB_ID(@CurrentDatabase), @CurrentObjectID, @CurrentIndexID, @CurrentPartitionNumber, 'LIMITED')

WHERE alloc_unit_type_desc = 'IN_ROW_DATA'

AND index_level = 0

SET @Error = @@ERROR

IF @Error = 1222

BEGIN

SET @ErrorMessage = 'The dynamic management view sys.dm_db_index_physical_stats is locked on the index ' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + '.' + QUOTENAME(@CurrentIndexName) + '.' + CHAR(13) + CHAR(10)

SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')

RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

GOTO NoAction

END

END

-- Select fragmentation group

IF @CurrentIndexID IS NOT NULL

BEGIN

SET @CurrentFragmentationGroup = CASE

WHEN @CurrentFragmentationLevel >= @FragmentationLevel2 THEN 'High'

WHEN @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 THEN 'Medium'

WHEN @CurrentFragmentationLevel < @FragmentationLevel1 THEN 'Low'

END

END

-- Which actions are allowed?

IF @CurrentIndexID IS NOT NULL

BEGIN

IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentAllowPageLocks = 1

BEGIN

INSERT INTO @CurrentActionsAllowed ([Action])

VALUES ('INDEX_REORGANIZE')

END

IF @CurrentOnReadOnlyFileGroup = 0

BEGIN

INSERT INTO @CurrentActionsAllowed ([Action])

VALUES ('INDEX_REBUILD_OFFLINE')

END

IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN(1,2) AND @CurrentIsLOB = 0 AND @CurrentIsPartition = 0 AND SERVERPROPERTY('EngineEdition') = 3

BEGIN

INSERT INTO @CurrentActionsAllowed ([Action])

VALUES ('INDEX_REBUILD_ONLINE')

END

END

-- Decide action

IF @CurrentIndexID IS NOT NULL

AND EXISTS(SELECT * FROM @ActionsPreferred)

AND (@CurrentPageCount >= @PageCountLevel OR @PageCountLevel = 0)

BEGIN

IF EXISTS(SELECT Priority, [Action], COUNT(*) FROM @ActionsPreferred GROUP BY Priority, [Action] HAVING COUNT(*) <> 3)

BEGIN

SELECT @CurrentAction = [Action]

FROM @ActionsPreferred

WHERE FragmentationGroup = @CurrentFragmentationGroup

AND Priority = (SELECT MIN(Priority)

FROM @ActionsPreferred

WHERE FragmentationGroup = @CurrentFragmentationGroup

AND [Action] IN (SELECT [Action] FROM @CurrentActionsAllowed))

END

ELSE

BEGIN

SELECT @CurrentAction = [Action]

FROM @ActionsPreferred

WHERE Priority = (SELECT MIN(Priority)

FROM @ActionsPreferred

WHERE [Action] IN (SELECT [Action] FROM @CurrentActionsAllowed))

END

END

-- Workaround for a bug in SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2

IF @CurrentIndexID IS NOT NULL

BEGIN

SET @CurrentMaxDOP = @MaxDOP

IF @Version < 11 AND @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @CurrentAllowPageLocks = 0

BEGIN

SET @CurrentMaxDOP = 1

END

END

-- Update statistics?

IF @CurrentStatisticsID IS NOT NULL

AND (@UpdateStatistics = 'ALL' OR (@UpdateStatistics = 'INDEX' AND @CurrentIndexID IS NOT NULL) OR (@UpdateStatistics = 'COLUMNS' AND @CurrentIndexID IS NULL))

AND (@CurrentStatisticsModified = 1 OR @OnlyModifiedStatistics = 'N')

AND ((@CurrentIsPartition = 0 AND (@CurrentAction NOT IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE') OR @CurrentAction IS NULL)) OR (@CurrentIsPartition = 1 AND @CurrentPartitionNumber = @CurrentPartitionCount))

BEGIN

SET @CurrentUpdateStatistics = 'Y'

END

ELSE

BEGIN

SET @CurrentUpdateStatistics = 'N'

END

-- Create comment

IF @CurrentIndexID IS NOT NULL

BEGIN

SET @CurrentComment = 'ObjectType: ' + CASE WHEN @CurrentObjectType = 'U' THEN 'Table' WHEN @CurrentObjectType = 'V' THEN 'View' ELSE 'N/A' END + ', '

SET @CurrentComment = @CurrentComment + 'IndexType: ' + CASE WHEN @CurrentIndexType = 1 THEN 'Clustered' WHEN @CurrentIndexType = 2 THEN 'NonClustered' WHEN @CurrentIndexType = 3 THEN 'XML' WHEN @CurrentIndexType = 4 THEN 'Spatial' ELSE 'N/A' END + ', '

SET @CurrentComment = @CurrentComment + 'LOB: ' + CASE WHEN @CurrentIsLOB = 1 THEN 'Yes' WHEN @CurrentIsLOB = 0 THEN 'No' ELSE 'N/A' END + ', '

SET @CurrentComment = @CurrentComment + 'AllowPageLocks: ' + CASE WHEN @CurrentAllowPageLocks = 1 THEN 'Yes' WHEN @CurrentAllowPageLocks = 0 THEN 'No' ELSE 'N/A' END + ', '

SET @CurrentComment = @CurrentComment + 'PageCount: ' + ISNULL(CAST(@CurrentPageCount AS nvarchar),'N/A') + ', '

SET @CurrentComment = @CurrentComment + 'Fragmentation: ' + ISNULL(CAST(@CurrentFragmentationLevel AS nvarchar),'N/A')

END

-- Check time limit

IF GETDATE() >= DATEADD(ss,@TimeLimit,@StartTime)

BEGIN

SET @Execute = 'N'

END

IF @CurrentIndexID IS NOT NULL AND @CurrentAction IS NOT NULL

BEGIN

SET @CurrentCommand01 = 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName)

IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE')

BEGIN

SET @CurrentCommand01 = @CurrentCommand01 + ' REBUILD'

IF @CurrentIsPartition = 1 SET @CurrentCommand01 = @CurrentCommand01 + ' PARTITION = ' + CAST(@CurrentPartitionNumber AS nvarchar)

SET @CurrentCommand01 = @CurrentCommand01 + ' WITH ('

IF @SortInTempdb = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + 'SORT_IN_TEMPDB = ON'

IF @SortInTempdb = 'N' SET @CurrentCommand01 = @CurrentCommand01 + 'SORT_IN_TEMPDB = OFF'

IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @CurrentIsPartition = 0 SET @CurrentCommand01 = @CurrentCommand01 + ', ONLINE = ON'

IF @CurrentAction = 'INDEX_REBUILD_OFFLINE' AND @CurrentIsPartition = 0 SET @CurrentCommand01 = @CurrentCommand01 + ', ONLINE = OFF'

IF @CurrentMaxDOP IS NOT NULL SET @CurrentCommand01 = @CurrentCommand01 + ', MAXDOP = ' + CAST(@CurrentMaxDOP AS nvarchar)

IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 SET @CurrentCommand01 = @CurrentCommand01 + ', FILLFACTOR = ' + CAST(@FillFactor AS nvarchar)

IF @PadIndex = 'Y' AND @CurrentIsPartition = 0 SET @CurrentCommand01 = @CurrentCommand01 + ', PAD_INDEX = ON'

IF @PadIndex = 'N' AND @CurrentIsPartition = 0 SET @CurrentCommand01 = @CurrentCommand01 + ', PAD_INDEX = OFF'

SET @CurrentCommand01 = @CurrentCommand01 + ')'

END

IF @CurrentAction IN('INDEX_REORGANIZE')

BEGIN

SET @CurrentCommand01 = @CurrentCommand01 + ' REORGANIZE'

IF @CurrentIsPartition = 1 SET @CurrentCommand01 = @CurrentCommand01 + ' PARTITION = ' + CAST(@CurrentPartitionNumber AS nvarchar)

SET @CurrentCommand01 = @CurrentCommand01 + ' WITH ('

IF @LOBCompaction = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + 'LOB_COMPACTION = ON'

IF @LOBCompaction = 'N' SET @CurrentCommand01 = @CurrentCommand01 + 'LOB_COMPACTION = OFF'

SET @CurrentCommand01 = @CurrentCommand01 + ')'

END

EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @CurrentCommand01, @CurrentComment, 2, @Execute

SET @Error = @@ERROR

IF @Error <> 0 SET @CurrentCommandOutput01 = @Error

IF @CurrentDelay IS NOT NULL

BEGIN

WAITFOR DELAY @CurrentDelay

END

END

IF @CurrentStatisticsID IS NOT NULL AND @CurrentUpdateStatistics = 'Y'

BEGIN

SET @CurrentCommand02 = 'UPDATE STATISTICS ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' ' + QUOTENAME(@CurrentStatisticsName)

IF @StatisticsSample IS NOT NULL OR @StatisticsResample = 'Y' OR @CurrentNoRecompute = 1 SET @CurrentCommand02 = @CurrentCommand02 + ' WITH'

IF @StatisticsSample = 100 SET @CurrentCommand02 = @CurrentCommand02 + ' FULLSCAN'

IF @StatisticsSample IS NOT NULL AND @StatisticsSample <> 100 SET @CurrentCommand02 = @CurrentCommand02 + ' SAMPLE ' + CAST(@StatisticsSample AS nvarchar) + ' PERCENT'

IF @StatisticsResample = 'Y' SET @CurrentCommand02 = @CurrentCommand02 + ' RESAMPLE'

IF (@StatisticsSample IS NOT NULL OR @StatisticsResample = 'Y') AND @CurrentNoRecompute = 1 SET @CurrentCommand02 = @CurrentCommand02 + ','

IF @CurrentNoRecompute = 1 SET @CurrentCommand02 = @CurrentCommand02 + ' NORECOMPUTE'

EXECUTE @CurrentCommandOutput02 = [dbo].[CommandExecute] @CurrentCommand02, '', 2, @Execute

SET @Error = @@ERROR

IF @Error <> 0 SET @CurrentCommandOutput02 = @Error

END

NoAction:

-- Update that the index is completed

UPDATE @tmpIndexesStatistics

SET Completed = 1

WHERE IxID = @CurrentIxID

-- Clear variables

SET @CurrentCommandSelect02 = NULL

SET @CurrentCommandSelect03 = NULL

SET @CurrentCommandSelect04 = NULL

SET @CurrentCommandSelect05 = NULL

SET @CurrentCommandSelect06 = NULL

SET @CurrentCommandSelect07 = NULL

SET @CurrentCommandSelect08 = NULL

SET @CurrentCommand01 = NULL

SET @CurrentCommand02 = NULL

SET @CurrentCommandOutput01 = NULL

SET @CurrentCommandOutput02 = NULL

SET @CurrentIxID = NULL

SET @CurrentSchemaID = NULL

SET @CurrentSchemaName = NULL

SET @CurrentObjectID = NULL

SET @CurrentObjectName = NULL

SET @CurrentObjectType = NULL

SET @CurrentIndexID = NULL

SET @CurrentIndexName = NULL

SET @CurrentIndexType = NULL

SET @CurrentStatisticsID = NULL

SET @CurrentStatisticsName = NULL

SET @CurrentPartitionID = NULL

SET @CurrentPartitionNumber = NULL

SET @CurrentPartitionCount = NULL

SET @CurrentIsPartition = NULL

SET @CurrentIndexExists = NULL

SET @CurrentStatisticsExists = NULL

SET @CurrentIsLOB = NULL

SET @CurrentAllowPageLocks = NULL

SET @CurrentNoRecompute = NULL

SET @CurrentStatisticsModified = NULL

SET @CurrentOnReadOnlyFileGroup = NULL

SET @CurrentFragmentationLevel = NULL

SET @CurrentPageCount = NULL

SET @CurrentFragmentationGroup = NULL

SET @CurrentAction = NULL

SET @CurrentMaxDOP = NULL

SET @CurrentUpdateStatistics = NULL

SET @CurrentComment = NULL

DELETE FROM @tmpIndexExists

DELETE FROM @tmpStatisticsExists

DELETE FROM @tmpIsLOB

DELETE FROM @tmpAllowPageLocks

DELETE FROM @tmpNoRecompute

DELETE FROM @tmpStatisticsModified

DELETE FROM @tmpOnReadOnlyFileGroup

DELETE FROM @CurrentActionsAllowed

END

END

-- Update that the database is completed

UPDATE @tmpDatabases

SET Completed = 1

WHERE ID = @CurrentID

-- Clear variables

SET @CurrentID = NULL

SET @CurrentDatabase = NULL

SET @CurrentIsDatabaseAccessible = NULL

SET @CurrentMirroringRole = NULL

SET @CurrentCommandSelect01 = NULL

DELETE FROM @tmpIndexesStatistics

END

----------------------------------------------------------------------------------------------------

--// Log completing information //--

----------------------------------------------------------------------------------------------------

Logging:

SET @EndMessage = 'DateTime: ' + CONVERT(nvarchar,GETDATE(),120)

SET @EndMessage = REPLACE(@EndMessage,'%','%%')

RAISERROR(@EndMessage,10,1) WITH NOWAIT

----------------------------------------------------------------------------------------------------

END

GO