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';

Answer 1

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?