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

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

About these ads

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′

  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

  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