SQL Server – Fixing Incorrect Syntax Near ‘GO’ When Generating Scripts

dynamic-sqlsql serversql server 2014sql-server-2012t-sql

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?

Best Answer

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.

Related Question