Is This A Problem In The Sp_rename Function Or Sql Server Itself?

October 25, 2024 Post a Comment

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] (TestColumnNa

Solution 1:

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.

Solution 2:

If you want to fix this, you can do this:

IF EXISTS(SELECT*FROM sys.columns where name ='[MyColumn]'AND [object_id]=OBJECT_ID('[MyTable]'))
BEGINEXEC sp_rename 'MyTable.[[MyColumn]]]', 'MyColumn', 'COLUMN';
END

Don't ask me why it works, it just does.

Solution 3:

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

Solution 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

Solution 5:

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:

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

Lab Sql