Link to home
Start Free TrialLog in
Avatar of bibi92
bibi92Flag for France

asked on

Error on procedure for defrag indexes

Hello,

I use this procedure for defrag indexes and it executes successfully on all instances but in the instance which contains subscriptions, I have this error :
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '('.

This is the procedure :

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[IndexOptimize]    Script Date: 10/24/2010 18:14:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[IndexOptimize]
as

DECLARE @command nvarchar(Max)

SELECT @command = 'USE [?]
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

OPEN partitions;

WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid ;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

        IF @frag < 30.0
            SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
        IF @frag >= 30.0
            SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD'';
        IF @partitioncount > 1
            SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N''Executed: '' + @command;
    END;

CLOSE partitions;
DEALLOCATE partitions;

DROP TABLE #work_to_do;

'

print @command
EXEC sp_MSforeachdb @command

Thanks

bibi
Avatar of BurnieP
BurnieP
Flag of Canada image

I think you are missing a quote on this line :

SELECT @command = 'USE [?]
Avatar of bibi92

ASKER

Sorry, there is already  a quote on this line and this stored proc works on others instances.
Thanks
bibi
ASKER CERTIFIED SOLUTION
Avatar of BurnieP
BurnieP
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bibi92

ASKER

Thanks a lot bibi