SQL Server having a column issue in renaming

Question

I have a table and in the design mode, the column has [square brackets] around the column name. What is this from? I can't seem to delete it and it's causing issues.

enter image description here

Content blocked by your organization Reason: This Websense category is filtered: Personal Network Storage and Backup. URL: http://screencast.com/t/KQZAWzkt — swasheck, Apr 03 '13 at 20:07
Really? How are `[` and `]` causing "issues?" What "issues" are they causing? Unless you're using [this language](http://gbf.sourceforge.net/) in which `[` is a keyword. — swasheck, Apr 03 '13 at 20:09

score 9 · Accepted Answer · answered Apr 03 '13 at 20:01

9

Your column name uses a reserved keyword. If you don't like the square brackets, they're going to be required in a lot of scenarios, so maybe you should consider either (a) using a non-reserved word (like IsDefault) or (b) not using the designer. Maybe both.

answered Apr 03 '13 at 20:01

Aaron Bertrand

272,866
37
466
490

1

I ran into this issue one time when doing a table rename. `Exec sp_rename '[schema].[tablename]', '[newTable]'` will cause this. The distinction being that the first is a reference and the second is a definition. – Eric J. Price Apr 03 '13 at 20:19
1

@AaronBertrand This is the best answer conceptually, but to resolve the immediate issue I added the correct syntax for handling the renaming as an answer. I've run into this issue before and needed it so I think it could help someone searching for the same answer. – Eric J. Price Apr 03 '13 at 20:31

score 5 · Answer 2 · answered Apr 03 '13 at 20:29

5

To get rid of the brackets do...

Exec sp_rename '[schema].[table].[[DEFAULT]]]', 'BetterColumnName', 'Column';

answered Apr 03 '13 at 20:29

Eric J. Price

2,740
1
15
21

+1 good point... however, I don't have a problem clicking right in the column name field and renaming it manually (I can't save it without the brackets if I continue to choose a reserved keyword, but I don't explicitly need to use `sp_rename` if I want a more sensible name). Of course I'm using SSMS 2012; maybe previous versions weren't so friendly with this. – Aaron Bertrand Apr 03 '13 at 20:34
@AaronBertrand Ah, gotcha, I've never used design mode so I wasn't aware of that limitation. Object explorer and the sp_rename will allow me to change the column name to "DEFAULT" without the brackets, but I see your point. The situation I was in that merited this option was in renaming a table that was incorrectly renamed environment wide. Rather than using object explorer on 500+ databases I just ran a deployment script. ;) – Eric J. Price Apr 03 '13 at 20:48
Yep, makes sense. Modern versions of the tool should handle the OP's scenario just fine, but if they insist on using reserved keywords *and* 8-year old versions... – Aaron Bertrand Apr 03 '13 at 20:49

score 2 · Answer 3 · answered Apr 03 '13 at 20:02

2

In table Design mode, if you give any column name as Default, it will be automatically surounded by [], because Default is the reserved key word. You can delete it by right click the row and delete it.

answered Apr 03 '13 at 20:02

ljh

2,546
1
14
20

score 0 · Answer 4 · answered Apr 03 '13 at 20:01

0

Typically that's when the field has special characters in it (like a space). Try making it only characters and/or numbers and it should go away.

answered Apr 03 '13 at 20:01

Jeff B

535
1
6
15

score 0 · Answer 5 · answered Apr 03 '13 at 20:11

Identifiers that do not comply with all the rules for identifiers must be delimited in a Transact-SQL statement. For example:

SELECT *
FROM [My Table]      --Identifier contains a space and uses a reserved keyword.
WHERE [order] = 10   --Identifier is a reserved keyword.

Ref: MSDN for the specific documentation: http://msdn.microsoft.com/en-US/library/ms175874%28v=SQL.90%29.aspx

SQL Server having a column issue in renaming

5 Answers5