Cannot rename the column of a temp table

Question

I created a global temp table like this -

CREATE TABLE ##BigTable
(Nos varchar(10) null)

Then try to rename the Nos column like this -

EXEC sp_RENAME '##BigTable.Nos' , 'Numbers', 'COLUMN'

I got the error -

Either the parameter @objname is ambiguous or the 
claimed @objtype (COLUMN) is wrong.

Why could this be happening and how do I solve the problem ?

EXTRA stuff not exactly related to the question, but for reference.

I want to add this - I tried to create the global temp table using a fully qualified name like this -

CREATE TABLE [NotMyTempDataBase].[dbo].[##BigTable]
(Nos varchar(10) null)

Then, I tried to rename it using -

EXEC tempdb.sys.sp_rename N'[NotMyTempDataBase].[dbo].[##BigTable].Nos', 
N'Numbers', N'COLUMN';

Error - The qualified @oldname references a database other than the current database.

This is wrong. I realized that the temp table is created in the system database tempdb, even though you specify another DB name while creating it.

use this instead -

CREATE TABLE [tempdb].[dbo].[##BigTable]
(Nos varchar(10) null)

--SQL server message : Database name 'tempdb' ignored, referencing object in tempdb.

EXEC tempdb.sys.sp_rename N'[tempdb].[dbo].[##BigTable].Nos', 
N'Numbers', N'COLUMN';

As an aside and just out of curiosity, are you really going to store numbers in a `varchar` column? If so, why? — Andriy M, Jan 30 '14 at 19:10
@AndriyM - No. That is just an example to demonstrate the problem. — HappyCoder, Jan 30 '14 at 19:12

score 41 · Accepted Answer · answered Jan 30 '14 at 19:09

41

Ok, so the actual solution is:

EXEC tempdb.sys.sp_rename N'##BigTable.Nos', N'Numbers', N'COLUMN';

Since the #temp table (even a ##global temp table) lives in tempdb, you need to invoke sp_rename there.

But further questions to consider:

Why on earth are you using a ##global temp table? You know this effectively limits concurrency to ONE, right? What do you think will happen when two users call this code at the same time? Probably you want to use a #local temp table here, or maybe avoid #temp tables altogether.
Why do you have the need to change the column name halfway through the script? Either name it right in the first place, or keep referencing the old name. How is the script later on going to know you changed the name? For what purpose?

answered Jan 30 '14 at 19:09

Aaron Bertrand

272,866
37
466
490

Thanks. I tried it without the N also. What is the purpose of N here, other than it preventing some kind of problem in parsing the ## ? – HappyCoder Jan 30 '14 at 19:14
3

N should be prefixed against every single Unicode string you ever pass to SQL Server. This avoids implicit conversions in a lot of cases, and data loss in others. Since all metadata deals with Unicode, always use N to prefix strings that involve metadata. Please see [this question](http://stackoverflow.com/questions/10025032/what-is-the-meaning-of-the-prefix-n-in-t-sql-statements) and [this question](http://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar) for plenty more details I don't want to repeat in a comment. – Aaron Bertrand Jan 30 '14 at 19:15
In response to your questions - I am only doing this to learn about temp tables. For now, things like concurrency are not a concern for me. But, its good to know about the related issues too. Thanks. – HappyCoder Jan 30 '14 at 19:19
Another approach I have seen is to say this when stating out: USE tempdb; GO – charles ross Sep 25 '19 at 12:50

score -1 · Answer 2 · answered May 19 '19 at 07:28

-1

Also , This worked for me. It may helpful to someone

EXEC tempdb.sys.sp_rename N'#Tab1.Info', N'Numbers', N'COLUMN';

answered May 19 '19 at 07:28

Singaravelan

809
3
19
32

Cannot rename the column of a temp table

2 Answers2