1

this is a partial view of my script to generate the drop index for a particular index

   ,[DropIndexScript] = 'DROP INDEX ' + QUOTENAME(SI.name) + 
   + CHAR(13) + ' ON '  + 
    QUOTENAME(Schema_name(T.Schema_id)) +'.'+ QUOTENAME(T.name) + CHAR(10) + CHAR(13) + 'GO' + CHAR(13)

FROM sys.indexes I  
INNER JOIN (
              SELECT  Object_id
                     ,Schema_id
                     ,NAME
                     FROM sys.tables 

              UNION ALL 

              SELECT  Object_id
                     ,Schema_id
                     ,NAME
                     FROM sys.views  

            ) T

           ON T.Object_id = I.Object_id   

 INNER JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid 

the question is:

when I use this:

+ CHAR(10) + CHAR(13) + 'GO' + CHAR(13)

It works - generates this script:

DROP INDEX [IDX_ProdImages_GetProductListingPageDenormalisedData]
 ON [dbo].[ProductImages]

GO

when I don't use the + CHAR(10) + CHAR(13) +

enter image description here

Msg 102, Level 15, State 1, Line 38289 Incorrect syntax near 'GO'.

is there any other way to get this done?

  • 2
    Why are you using inconsistent newlines? First is CRLF, second is just LF. Also, you shouldn't need a GO between DROP statements anyway. – Solomon Rutzky Oct 7 '16 at 13:57
0

The problem is that you are mixing up which character is which in the variations of a newline: CRLF / LF. You have:

,[DropIndexScript] = 'DROP INDEX ' + QUOTENAME(SI.name) + 
   + CHAR(13) + ' ON '  + 
QUOTENAME(Schema_name(T.Schema_id)) +'.'+ QUOTENAME(T.name)
 + CHAR(10) + CHAR(13) + 'GO' + CHAR(13)

Here you use either CHAR(13) or CHAR(10) + CHAR(13). The problem is that CHAR(13) is a "Carriage Return" / "CR" while CHAR(10) is a "Line Feed" / "LF". You have reversed them and hence are using both "CR" and "LFCR" in various places. If you swap the "10"s for the "13"s it would work correctly.

Better yet would be having consistency across them. Either use just CHAR(10) everywhere, or CHAR(13) + CHAR(10) everywhere.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.