is this a problem in the sp_rename function or sql server itself ?

Question

While renaming the column name, the square bracket is included in the column name, which I think is a bug, Here is a sample code snippet,

 create table [TestTable]

(TestColumnName nvarchar(30))

select TestColumnName from TestTable

sp_rename '[TestTable].[TestColumnName]', '[RenamedColumnName]', 'Column'

select [RenamedColumnName] from TestTable -- does not work "Invalid column name 'RenamedColumnName'."

select RenamedColumnName from TestTable -- does not work "Invalid column name 'RenamedColumnName'."

select * from [TestTable]  -- works fine!!!

The bug here is that the column rename includes the square brackets, I found this which says that the "first character must be one of the following", but "[" does not seem be included in the list, is there a problem with sp_rename or sql server itself?, as it allows alteration of column name to start with a square bracket.

Answer 1

the square brackets are used to mark the boundaries of the columns names. That way you can include reserved words, spaces, single quotes etc. in the column names and the script will not fail.

sp_rename: http://msdn.microsoft.com/en-us/library/aa238878(SQL.80).aspx

from BOL: This example renames the contact title column in the customers table to title: EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

try this:

sp_rename 'TestTable.TestColumnName', 'RenamedColumnName', 'Column'

since you are passing in strings into the procedure you don't need the square braces "[","]"

you can use "[","]" in the first parameter, but if you use them in the second parameter, they become part of the actual column name:

create table [TestTable2]([Test ColumnName] nvarchar(30))
exec sp_help testtable2
exec sp_rename 'dbo.TestTable2.[Test ColumnName]', 'Renamed ColumnName', 'Column'
exec sp_help testtable2

Answer 2

Data error !!!

Its not

sp_rename '[TestTable].[TestColumnName]', '[RenamedColumnName]', 'Column'

It should be like this

sp_rename '[TestTable].[TestColumnName]', 'RenamedColumnName', 'Column'

then

select [RenamedColumnName] from TestTable -- works fine!!!

select RenamedColumnName from TestTable -- works fine!!!

select * from [TestTable]  -- works fine!!!

Even though the new column name is with space like "Renamed ColumnName" NO NEED TO use the square brackets in the

Answer 3

The column in your code has been renamed to one that actually includes [] - to query this column you'll have to use

SELECT [[RenamedColumnName]]] FROM TestTable

] is a delimited identifier, so you have to escape it. For ], this means an additional ] for each one used in the name.

Answer 4

It's not a bug, as "[" and "]" are valid characters within a column name. sp_rename has to work by receiving the exact column name you want to use - after all how would it know whether you wanted a column actually called "[MyColumnWithBrackets]" or "MyColumnWithBrackets". Hence, if you provide a name, it's treated literally and does not require you to manually enclose (e.g.) column names with spaces in, in brackets