How to rename something in SQL Server that has square brackets in the name?

12
votes

I have a column in one of my tables which has square brackets around it, [Book_Category], which I want to rename to Book_Category.

I tried the following query:

sp_rename 'BookPublisher.[[Book_Category]]', 'Book_Category', 'COLUMN'

but I got this error:

Msg 15253, Level 11, State 1, Procedure sp_rename, Line 105 Syntax error parsing SQL identifier 'BookPublisher.[[Book_Category]]'.

Can anyone help me?

4
sqlsql-serversql-server-2008
Show us some code please. - Max
In general: When in doubt as to SQL syntax or escaping, do it in the GUI and click the options to make it generate a script. - Moby Disk

4 Answers

23
votes

You do it the same way you do to create it:

exec sp_rename 'BookPublisher."[Book_Category]"', 'Book_Category', 'COLUMN';

Here's a little sample I made to test if this was even possible. At first I just assumed it was a misunderstanding of how [] can be used in SQL Server, turns out I was wrong, it is possible - you have to use double quotes to outside of the brackets.

begin tran

create table [Foo] ("[i]" int);

exec sp_help 'Foo';

exec sp_rename 'Foo."[i]"', 'i', 'column ';

exec sp_help 'Foo';

rollback tran
2
votes

Double quotes are not required. You simply double up closing square brackets, like so:

EXEC sp_rename 'BookPublisher.[[Book_Category]]]', 'Book_Category', 'COLUMN';

You can find this out yourself using the quotename function:

SELECT QuoteName('[Book_Category]');
-- Result: [[Book_Category]]]

This incidentally works for creating columns, too:

CREATE TABLE dbo.Book (
   [[Book_Category]]] int -- "[Book_Category]"
);
1
votes

this works for me:

exec sp_rename ‘[dbo].[TableName].[OldColumnName]’, ‘NewColumnName’
0
votes

Got that error, and was wondering why. You can get this exact error if the column name you are changing from doesn't exist. Might as well doubly make sure if you still get that error.