Just a day ago, my old colleague sent me an email.
“I accidently renamed my tablename as a , and now I am not able to rename it back with the help of T-SQL to its original name which was ProjectA. Is there any way to fix it?”
Very interesting question. If you want to rename your table name, you can use sp_rename procedure to rename your table. Here is an earlier blog post, I have written on this subject SQL SERVER – How to Rename a Column Name or Table Name. However, if you have [ or ] in the name, you can not straight forward use the SP and rename the table.
First, we will try our usual way to rename the table.
sp_rename '', 'ProjectA';
The above query will give us following error:
Msg 15253, Level 11, State 1, Procedure sp_rename, Line 107
Syntax error parsing SQL identifier ‘‘.
This is because our table name contains Identifier [ as well as Identifier ].
If your table name contains identifier, and if you want to rename it, you should run wrap the tablename with double quotes (“) and use the sp_rename command. Here is the example
sp_rename '""', 'ProjectA';
When we run above query, it will give us success message and rename our table to the new name.
Let me know if you follow any other method to rename table when there is identifier in the tablename.
Reference: Pinal Dave (http://blog.sqlauthority.com)