sql server 2008: sp_RENAME table disappeared

I renamed the table by 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.

:) 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 ":)".

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

  USE dbname SELECT OBJECT_ID('scname.newtblnam')

• sp_rename says

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)

• 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

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.

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

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'