sql server 2008：sp_RENAME表消失了

I renamed the table by sp_RENAME (SQL SERVER 2008)

我通过sp_RENAME重命名了表（SQL SERVER 2008）

sp_RENAME 'dbname.scname.oldtblname' 'dbname.scname.newtblnam'

The resulting message (it was in black color - so I get it as just a warning or successful message) was something like "Caution: Changing any part of an object name could break scripts and stored procedures."

结果消息（它是黑色的 - 所以我只是一个警告或成功的消息）是类似“警告：更改对象名称的任何部分可能会破坏脚本和存储过程。”

So after this command my table with 45 million records just disappeared and I don't have any backup. This was a new table.

所以在这个命令之后我的表有4500万条记录消失了，我没有任何备份。这是一张新桌子。

:) Do you guys have any idea about bringing my table back? :)

:)你们有什么想要把我的桌子带回来吗？ :)

p.s. Yeah, my smile is not ":(", because when the seriousness of the problem goes up the threshold, ":(" becomes ":)".

附：是的，我的笑容不是“:(”，因为当问题的严重性达到阈值时，“:(”变成“:)”。

3 个解决方案

#1

What does this say?
SSMS does not refresh Object explorer automatically so it could be there

这说什么？ SSMS不会自动刷新对象资源管理器，因此它可能存在

USE dbname SELECT OBJECT_ID('scname.newtblnam')

USE dbname SELECT OBJECT_ID（'scname.newtblnam'）
sp_rename says

sp_rename说

You can change the name of an object or data type in the current database only. The names of most system data types and system objects cannot be changed.

您只能在当前数据库中更改对象或数据类型的名称。大多数系统数据类型和系统对象的名称都无法更改。

You specified dbname so it's possible you have an object [dbname.scname.newtblnam] in dbo schema (or similar)

您指定了dbname，因此您可能在dbo架构（或类似）中有一个对象[dbname.scname.newtblnam]

And did you do a backup first? Best practice before any (formal) schema changes, y'know
你先做了备份吗？你知道，在任何（正式）架构发生变化之前的最佳实践

FWIW, I've never lost a table or other object using sp_rename

FWIW，我从未使用sp_rename丢失表或其他对象

#2

Faced that awful bug too, here is the solution:

面对那个可怕的错误，这里是解决方案：

--original
sp_RENAME 'dbname.scname.oldtblname', 'dbname.scname.newtblnam' 

--workaround 
sp_RENAME 'dbname.scname.[dbname.scname.newtblnam]','oldtblname'

name in [] is actually your TABLE name after sp_rename, to make SQL server to understand it put it in square brackets.

[]中的名称实际上是sp_rename之后的TABLE名称，以使SQL服务器理解它将其放在方括号中。

#3

I found this a little unclear, but reading the docs for sp_rename reveals that

我发现这有点不清楚，但阅读sp_rename的文档可以发现这一点

exec sp_rename 'udt.TenorSymbol_t_temp', 'udt.TenorSymbol_t', 'USERDATATYPE'

will create a type

将创建一个类型

udt.udt.TenorSymbol_t

So to create the type in the correct schema, don't specify the schema on rename

因此，要在正确的模式中创建类型，请不要在重命名时指定模式

exec sp_rename 'udt.TenorSymbol_t_temp', 'TenorSymbol_t', 'USERDATATYPE'

#1

What does this say?
SSMS does not refresh Object explorer automatically so it could be there

这说什么？ SSMS不会自动刷新对象资源管理器，因此它可能存在

USE dbname SELECT OBJECT_ID('scname.newtblnam')

USE dbname SELECT OBJECT_ID（'scname.newtblnam'）
sp_rename says

sp_rename说