CODEWITHSUNDEEP
sqlsql-server
Gertjan Gijsbers
Gertjan Gijsbers

Reputation: 43

Mistake made while changing column name using sp_rename. (double colon created)

I made a mistake while changing the name of a column. I wanted to change a column name in my SQL database (Microsoft SQL Server). I succeeded, but I put a extra colon between my column name. That's why I can't change the name again now. The use of square brackets didn't solve the issue.

Instead of "table.column_name" does it says "table.table.column_name".

This also prevents me from executing 'select', 'drop' or 'update' statements. This is because the column is no longer found, giving me the error "No item by the name of '[table.new_scheme]' could be found in the current database 'name_database'"

What I did:

exec sp_rename 'table.old_column', 'table.new_column'

What it supposed to be:

exec sp_rename 'table.old_column', 'new_column'

I read and used this article (in the wrong way :|). How to rename a table in SQL Server?

Upvotes: 1

Views: 428

Answers (4)

Venkataraman R
Venkataraman R

Reputation: 12969

You can do this using SSMS object explorer. Snapshot is given below:

Rename column in SSMS Object Explorer

Upvotes: 0

eshirvana
eshirvana

Reputation: 24603

now you have to do this , so you put your column new which is "table.new_column" right now in double-quote or rectangular brackets like so :

exec sp_rename 'dbo.tablename."table.new_column"', 'new_column', 'COLUMN';

or

exec sp_rename 'dbo.tablename.[table.new_column]', 'new_column', 'COLUMN';

Upvotes: 1

Thom A
Thom A

Reputation: 95830

You need to delimit identify the column inside the literal string:

CREATE TABLE dbo.YourTable (YourColumn int);
GO
EXEC sys.sp_rename N'dbo.YourTable.YourColumn',N'YourTable.NewColumn','COLUMN';
GO
SELECT *
FROM dbo.YourTable;
GO
EXEC sp_rename N'dbo.YourTable.[YourTable.NewColumn]', N'NewColumn', 'COLUMN' 
GO
SELECT *
FROM dbo.YourTable;
GO
DROP TABLE dbo.YourTable;

Upvotes: 0

VBoka
VBoka

Reputation: 9083

Lets say you have a simple table:

create table mytable (id int, test int)

And you changed the name of the second column:

exec sp_rename 'mytable.test', 'mytable.test2'

Here is how you can rename it again:

exec sp_rename 'mytable."mytable.test2"', 'test2'

Here is a demo:

DEMO

Upvotes: 0

Related Questions