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

Yesterday I posted article where we discussed how to rename a table name where identifier is part of the tablename. Read the blog post Rename a Table Name Containing [ or ] in the Name – Identifier in the Table Name. If the table name contains an identifier, it is not easy to rename a table, the default method will show an error as displayed below.

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 ]. One of the method was to rename table was to use Double Quotes around the identifier.

sp_rename '"[]"', 'ProjectA';

When we run above query, it will give us success message and rename our table to the new name.

At the end of the blog post, I asked if there is any other way to the same task.

SQL Expert Parth Malhan answered in the comment with alternative solution where he demonstrates that we can use identifier around the name of the table and rename the column as well.

EXEC sys.sp_rename '[[]]]','ProjectA'

Thanks Parth, very cool trick!

What to Check Before You Rename a Table Name With Brackets

Square brackets are the default way SQL Server quotes a name, so a name that holds [ or ] confuses the parser. Inside a bracket quoted name, a closing bracket has to be written twice. A table called Sales]Data is written as [Sales]]Data]. If you do not want to escape it by hand, QUOTENAME() does it for you and returns the name ready to use.

The second parameter of sp_rename needs care too. SQL Server takes the new name exactly as you type it, so if you wrap it in brackets, the brackets become part of the new name. That is how many of these odd names get created in the first place.

After the change, run SELECT name FROM sys.tables and confirm the new name looks exactly as you expect. Also remember that sp_rename does not update views, procedures or application code that still use the old name, so search for those before you run it on a real server. My simple advice: stick to letters, numbers and underscores in object names, and you will never need these tricks.

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