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.

“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 ‘[]’.

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

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 (https://blog.sqlauthority.com)

SQL Scripts, SQL Server Management Studio
Previous Post
SQL SERVER – Writing SQL Queries Made Easy with dbForget SQL Complete
Next Post
SQL SERVER – Rename a Table Name Containing [ or ] Identifier in the Name – Part 2

Related Posts

4 Comments. Leave new

  • We can also use following Script.
    This one i get from SQL Server Table Designer (Generate Script)

    CREATE TABLE Table1 (id INT)
    EXEC sys.sp_rename ‘Table1′,'[]’
    EXEC sys.sp_rename ‘[[]]]’,’Table1′

    Reply
  • Hi Sir, Good Morning.. This is Vishal…. I m facing a problem of error when connecting SQL MSF file in C# 2008 error 655….. sql error please give me solution for this problem m weak in english try to understand sir

    thanks in advance

    On 25/01/2014, Journey to SQL Authority with Pinal Dave

    Reply

Leave a ReplyCancel reply

Exit mobile version