How do I fetch multiple columns for use in a cursor loop?

Question

When I try to run the following SQL snippet inside a cursor loop,

set @cmd = N'exec sp_rename ' + @test + N',' +            RIGHT(@test,LEN(@test)-3) + '_Pct' + N',''COLUMN''' 

I get the following message,

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 213
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

What is wrong and how do I fix it ? I tried wrapping the column name in brackets [], and double quotes "" like some of the search results suggested.

Edit 1 -

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.

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 

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".

Answer 1

Here is slightly modified version. Changes are noted as code commentary.

BEGIN TRANSACTION  declare @cnt int declare @test nvarchar(128) -- variable to hold table name declare @tableName nvarchar(255) declare @cmd nvarchar(500)  -- local means the cursor name is private to this code -- fast_forward enables some speed optimizations declare Tests cursor local fast_forward for  SELECT COLUMN_NAME, TABLE_NAME    FROM INFORMATION_SCHEMA.COLUMNS    WHERE COLUMN_NAME LIKE 'pct%'      AND TABLE_NAME LIKE 'TestData%'  open Tests -- Instead of fetching twice, I rather set up no-exit loop while 1 = 1 BEGIN   -- And then fetch   fetch next from Tests into @test, @tableName   -- And then, if no row is fetched, exit the loop   if @@fetch_status <> 0   begin      break   end   -- Quotename is needed if you ever use special characters   -- in table/column names. Spaces, reserved words etc.   -- Other changes add apostrophes at right places.   set @cmd = N'exec sp_rename '''             + quotename(@tableName)             + '.'             + quotename(@test)             + N''','''             + RIGHT(@test,LEN(@test)-3)             + '_Pct'''             + N', ''column'''     print @cmd    EXEC sp_executeSQL @cmd END  close Tests  deallocate Tests  ROLLBACK TRANSACTION --COMMIT TRANSACTION