title: Build of varchar(max) command variable is truncated
body: <p>Like many others here I don't have a lot of experience under my belt.  I've been directed to convert non clustered indexes to clustered  (No clustered indexes exist).  In the attached query I print out the @sql variable to see what my command looks like and it's only about half the tables.</p>
  
  <p>1st question.  Is there a limit on how long a string that can be executed or printed?</p>
  
  <p>I tried commenting out SET @sql = @sql + ' UNION ALL ' in hopes I could print or execute one command at a time but nothing printed.  I really don't want to execute until I'm confident I have the right syntax.  </p>
  
  <pre><code>SET NOCOUNT ON
  IF OBJECT_ID('tempdb..##ListIndex') IS NOT NULL DROP TABLE ##ListIndex
  Create Table ##ListIndex (MySchema nvarchar(max), MyTable nvarchar(max), MyIndexName nvarchar(max), MyColumn nvarchar(max), IndexType int) 
  Insert into ##ListIndex
  select s.name, t.name, i.name, c.name, i.type
   from sys.tables t
  inner join sys.schemas s on t.schema_id = s.schema_id
  inner join sys.indexes i on i.object_id = t.object_id
  inner join sys.index_columns ic on ic.object_id = t.object_id
      inner join sys.columns c on c.object_id = t.object_id and
          ic.column_id = c.column_id
  where i.index_id =2
  and i.type in (1, 2) -- clustered &amp; nonclustered only
  
  and i.is_disabled = 0
  and i.is_hypothetical = 0
  and ic.key_ordinal &gt; 0
  
  order by s.name asc, t.name desc --ic.key_ordinal
  
  
  IF OBJECT_ID('tempdb..##ListIndex2') IS NOT NULL DROP TABLE ##ListIndex2
  Create Table ##ListIndex2 (MySchema nvarchar(max), MyTable nvarchar(max), MyIndexName nvarchar(max), MyColumn nvarchar(max))
  
  Insert into ##ListIndex2
  SELECT  DISTINCT
          MySchema
  ,       MyTable
  ,       MyIndexName
  ,       STUFF(
                  (
                      SELECT ', ' + MyColumn + ' ASC'
                      FROM    ##ListIndex TInner  -- replace with your table
                      WHERE   TOuter.MyTable      = TInner.MyTable
                      AND     TOuter.MyIndexName  = TInner.MyIndexName
                      AND     TOuter.MySchema     = TInner.MySchema
                      FOR XML PATH('')
                  ), 1, 2, ''
              ) MyColumn
  
  FROM    ##ListIndex TOuter 
  --select * from ##ListIndex2
  
  
  SET NOCOUNT ON
  
  DECLARE MyCursor CURSOR FOR
  SELECT 
      MySchema
      , MyTable
      , MyIndexName
      ,MyColumn
  FROM ##ListIndex2 c
  
  
  OPEN MyCursor
  
  DECLARE @MySchema VARCHAR(100), @MyTable VARCHAR(100), @MyIndexName VARCHAR(100), @MyColumn VARCHAR(300)
  DECLARE @sql VARCHAR(MAX)='';
  
  FETCH FROM MyCursor INTO @MySchema, @MyTable, @MyIndexName, @MyColumn
  WHILE @@FETCH_STATUS=0
  BEGIN   
      IF LEN(@sql) &gt; 0 
      SET @sql = @sql + ' UNION ALL '
      SET @sql= @sql + 'CREATE UNIQUE CLUSTERED INDEX ' + @MyIndexName + ' on ' + @MySchema + '.' + @MyTable +' (' + @MyColumn +') WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON,ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [DefFG]  GO  '
  
      FETCH FROM MyCursor INTO @MySchema, @MyTable, @MyIndexName, @MyColumn
  
  END
  
  CLOSE MyCursor
  DEALLOCATE MyCursor
  
  --exec sp_executesql @sql
  
  print @sql
  </code></pre>