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

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

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.

renameerror2 SQL SERVER   Rename a Table Name Containing [ or ] in the Name   Identifier in the Table 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)

4 thoughts on “SQL SERVER – Rename a Table Name Containing [ or ] in the Name – Identifier in the Table Name

  1. 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′

    Like

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

    Like

  3. Pingback: SQL SERVER – Rename a Table Name Containing [ or ] Identifier in the Name – Part 2 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s