How to get multiple columns to use in a cursor loop?

Question

When I try to execute the following piece of SQL code 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 @objname parameter @objname ambiguous, or the declared @objtype (COLUMN) is @objtype .

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

Change 1 -

Here is the whole script. How to pass table name in rename sp? I'm not sure how to do this, 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 

Change 2 - the script is designed to rename columns whose names match the pattern, in this case with the prefix "pct". Columns are found in different tables in the database. All table names are prefixed with "TestData".

Answer 1

Here is a slightly modified version. Changes are marked as code comments.

 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 

Answer 2

You say to rename a column, but it does not know which table the column is in.

sp_rename :

[ @objname = ] 'object_name'

Is the current qualified or unqualified name of the user object or data type. If the object to be renamed is a column in the table, the object_name must be in the form of table.column or schema.table.column .

(emphasis added)