SQL SERVER – Rename a Table Name Containing [ or ] in the Name – Identifier in the Table Name

Just a day ago, my old colleague sent me an email. He could not rename a table whose name had turned into []. Here is how to rename a table name containing [ or ] characters.

“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.

Tips to Rename a Table Name Containing Brackets Safely

Another way that works is to escape the brackets yourself. Inside square brackets, a closing bracket is written twice, so the table named [] can be written as [[]]]. If you are not sure how to escape a name, let QUOTENAME('[]') build it for you.

Two more tips. Include the schema in the old name, such as 'dbo.OldName', but never in the new name, or the schema becomes part of the table name. And remember that sp_rename does not update views, procedures or functions that use the old name. Check sys.sql_expression_dependencies before you rename a table in a busy database. sp_rename even prints a caution about this every time it runs, and that message is worth reading.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.