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 ":)".
sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename . sp_rename can be used to rename primary and secondary XML indexes.
ALTER TABLE table_name RENAME TO new_table_name; Columns can be also be given new name with the use of ALTER TABLE.
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)
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With