无法重命名临时表的列

I created a global temp table like this -

我创建了一个像这样的全局临时表 -

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

Then try to rename the Nos column like this -

然后尝试重命名Nos列,如下所示 -

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.

EXTRA与问题不完全相关的东西,但供参考。

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.

错误 - 限定的@oldname引用当前数据库以外的数据库。

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.

这是错的。我意识到临时表是在系统数据库tempdb中创建的,即使您在创建它时指定了另一个DB名称。

use this instead -

改用它 -

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

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

--SQL服务器消息:忽略数据库名称'tempdb',引用tempdb中的对象。

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

1 个解决方案

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

由于#temp表(甚至是##全局临时表)都存在于tempdb中,因此需要在那里调用sp_rename。

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.

为什么你在使用##全局临时表?你知道这有效地将并发限制为ONE,对吗?当两个用户同时调用此代码时,您认为会发生什么?可能你想在这里使用#local临时表,或者完全避免使用#temp表。
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?

为什么需要在脚本中途更改列名?要么首先将其命名,要么继续引用旧名称。以后脚本怎么会知道你更改了名字?出于什么目的?

#1