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.

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

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)

,
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 Reply

Menu