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

Question

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?

Answer 1

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

Answer 2

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]"
);

Answer 3

this works for me:

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