I am getting the error when executing in SQL 2008 the following procedure:
USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spIndexfragmentationCheck] (
@CheckFrag BIT --1 Show Output, 0 Fix Fragmentation
)
AS
SET NOCOUNT ON
DECLARE @cmd NVARCHAR(1000),
@TableName VARCHAR(255),
@DatabaseName VARCHAR(255),
@SchemaName VARCHAR(255),
@IndexName VARCHAR(255),
@AvgFragmentationInPercent DECIMAL,
@FillFactor INT,
@FragmentationThresholdForReorganizeTableLowerLimit VARCHAR(10),
@FragmentationThresholdForRebuildTableLowerLimit VARCHAR(10),
@dbName VARCHAR(50),
@pageLocks BIT
SET @FillFactor = 90 --Set for Query Optimization, DO NOT EDIT
SET @FragmentationThresholdForReorganizeTableLowerLimit = '10.0' --Percent
SET @FragmentationThresholdForRebuildTableLowerLimit = '35.0' --Percent
--Make sure temp table is deleted
IF (SELECT OBJECT_ID('tempdb..#Index_Frag')) IS NOT NULL
DROP TABLE #Index_Frag
DECLARE Roy CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM master..sysdatabases
WHERE Name NOT IN ('tempdb', 'master','msdb','model')
AND (version IS NULL OR version > 0)
OPEN Roy
FETCH NEXT FROM Roy INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @CheckFrag = 0
BEGIN
SET @cmd = 'USE '+@dbName+' INSERT INTO DB..IndexFragmentation
SELECT OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent AS [PercentFragmented], SI.name [IndexName], '''+@dbName+''' AS [DatabaseName],
schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed, GETDATE(), NULL, si.allow_page_locks AS Page_Lock_Enabled
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IPS
JOIN sys.tables ST WITH (NOLOCK) ON IPS.OBJECT_ID = ST.OBJECT_ID
JOIN sys.indexes SI WITH (NOLOCK) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL
AND avg_fragmentation_in_percent >= CONVERT(DECIMAL, '+@FragmentationThresholdForReorganizeTableLowerLimit+')'
END
ELSE
BEGIN
SET @cmd = 'USE '+@dbName+'
SELECT OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent AS [PercentFragmented], SI.name [IndexName], '''+@dbName+''' AS [DatabaseName],
schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed, GETDATE(), NULL, si.allow_page_locks AS Page_Lock_Enabled
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IPS
JOIN sys.tables ST WITH (NOLOCK) ON IPS.OBJECT_ID = ST.OBJECT_ID
JOIN sys.indexes SI WITH (NOLOCK) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL
AND avg_fragmentation_in_percent >= CONVERT(DECIMAL, '+@FragmentationThresholdForReorganizeTableLowerLimit+')
ORDER BY avg_fragmentation_in_percent DESC'
END
EXEC (@cmd)
FETCH NEXT FROM Roy INTO @dbName
END
CLOSE Roy
DEALLOCATE Roy
BEGIN TRY
DECLARE Roy CURSOR FAST_FORWARD FOR
SELECT TableName, PercentFragmented, DatabaseName, SchemaName, IndexName, AllowPageLocks FROM DB..IndexFragmentation
WHERE IsProcessed = 0
OPEN Roy
FETCH NEXT FROM Roy INTO @TableName, @AvgFragmentationInPercent, @DatabaseName, @SchemaName, @IndexName, @pageLocks
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'WORKING ON INDEX: ' + @IndexName + ' ON TABLE ' + RTRIM(LTRIM(@DatabaseName)) + '.' + RTRIM(LTRIM(@SchemaName)) + '.' + RTRIM(LTRIM(@TableName))
--ENABLE PAGE LOCKS
SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + RTRIM(LTRIM(@DatabaseName)) + '.' + RTRIM(LTRIM(@SchemaName)) + '.' + RTRIM(LTRIM(@TableName)) + ' SET (ALLOW_PAGE_LOCKS = ON);'
EXEC (@cmd)
--REORGANIZE INDEX
IF((@AvgFragmentationInPercent >= @FragmentationThresholdForReorganizeTableLowerLimit) AND (@AvgFragmentationInPercent < @FragmentationThresholdForRebuildTableLowerLimit))
BEGIN
SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + RTRIM(LTRIM(@DatabaseName)) + '.' + RTRIM(LTRIM(@SchemaName)) + '.' + RTRIM(LTRIM(@TableName)) + ' REORGANIZE'
EXEC (@cmd)
END
--REBUILD INDEX
ELSE IF (@AvgFragmentationInPercent >= @FragmentationThresholdForRebuildTableLowerLimit)
BEGIN
SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + RTRIM(LTRIM(@DatabaseName)) + '.' + RTRIM(LTRIM(@SchemaName)) + '.' + RTRIM(LTRIM(@TableName)) + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@FillFactor) + ', STATISTICS_NORECOMPUTE = OFF)'
EXEC (@cmd)
END
--CHECK IF PAGE LOCKS WERE DISABLED BEFORE
IF @pageLocks = 0
BEGIN
SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + RTRIM(LTRIM(@DatabaseName)) + '.' + RTRIM(LTRIM(@SchemaName)) + '.' + RTRIM(LTRIM(@TableName)) + ' SET (ALLOW_PAGE_LOCKS = OFF);'
EXEC (@cmd)
END
--UPDATE THE TABLE TO REFLECT THAT THE INDEX WAS PROCESSED SUCCESSFULLY
UPDATE DB..IndexFragmentation SET IsProcessed = 1, ProcessedDateTime = GETDATE()
WHERE TableName = @TableName AND IndexName = @IndexName AND CONVERT(VARCHAR(10), Added,110) = CONVERT(VARCHAR(10), GETDATE(), 110)
FETCH NEXT FROM Roy INTO @TableName, @AvgFragmentationInPercent, @DatabaseName, @SchemaName, @IndexName, @pageLocks
END
CLOSE Roy
DEALLOCATE Roy
END TRY
BEGIN CATCH
PRINT 'DATE: ' + CONVERT(VARCHAR, GETDATE()) + ' There is some run time exception.'
PRINT 'ERROR CODE: ' + CONVERT(VARCHAR, ERROR_NUMBER())
PRINT 'ERROR MESSAGE: ' + ERROR_MESSAGE()
END CATCH