title: How do I fetch multiple columns for use in a cursor loop?
body: <p>When I try to run the following SQL snippet inside a cursor loop, </p>
  
  <pre><code>set @cmd = N'exec sp_rename ' + @test + N',' +
             RIGHT(@test,LEN(@test)-3) + '_Pct' + N',''COLUMN'''
  </code></pre>
  
  <p>I get the following message,</p>
  
  <blockquote>
    <p>Msg 15248, Level 11, State 1, Procedure sp_rename, Line 213<br>
       Either the parameter <code>@objname</code> is ambiguous or the claimed <code>@objtype</code> (COLUMN) is wrong.</p>
  </blockquote>
  
  <p>What is wrong and how do I fix it ? I tried wrapping the column name in brackets <code>[]</code>, and double quotes <code>""</code> like some of the search results suggested. </p>
  
  <p>Edit 1 -</p>
  
  <p>Here is the entire script. How do I pass the table name to the rename sp ? I'm not sure how to do that since the column names are in one of many tables.</p>
  
  <pre><code>BEGIN TRANSACTION
  
  declare @cnt int
  declare @test nvarchar(128)
  declare @cmd nvarchar(500) 
  declare Tests cursor for
  SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'pct%' AND TABLE_NAME LIKE 'TestData%'
  
  open Tests
  fetch next from Tests into @test
  while @@fetch_status = 0
  BEGIN
    set @cmd = N'exec sp_rename ' + @test + N',' + RIGHT(@test,LEN(@test)-3) + '_Pct' + N', column' 
  
    print @cmd
  
    EXEC sp_executeSQL @cmd
  
    fetch next from Tests into @test
  END
  
  close Tests 
  deallocate Tests
  
  
  ROLLBACK TRANSACTION
  --COMMIT TRANSACTION
  </code></pre>
  
  <p>Edit 2 - 
  The script is designed to rename columns whose names match a pattern, in this case with a "pct" prefix. The columns occur in a variety of tables within the database. All table names are prefixed with "TestData".</p>