Yesterday I posted article where we discussed how to rename a table name where identifier is part of the tablename. Read the blog post Rename a Table Name Containing [ or ] in the Name – Identifier in the Table Name. If the table name contains an identifier, it is not easy to rename a table, the default method will show an error as displayed below.
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 ]. One of the method was to rename table was to use Double Quotes around the identifier.
sp_rename '""', 'ProjectA';
When we run above query, it will give us success message and rename our table to the new name.
At the end of the blog post, I asked if there is any other way to the same task.
SQL Expert Parth Malhan answered in the comment with alternative solution where he demonstrates that we can use identifier around the name of the table and rename the column as well.
EXEC sys.sp_rename '[]]','ProjectA'
Thanks Parth, very cool trick!
Reference: Pinal Dave (http://blog.sqlauthority.com)