Well, that’s a pretty simple requirement if you are a database developer. Surprisingly I was only aware of how to do this using SQL Server Enterprise Manager. But one of my friends asked me how to do it using SQL Script. The requirement was that we cannot drop the existing table and recreate it again as the table has a lot of data. A little googling helped to figure out that it’s pretty simple.
There is an inbuilt system stored procedure “sp_rename” which can be used to accomplish this task. Infact this system stored procedure can be used to rename a user created object like table, column or user-defined data-type.
The syntax is pretty simple:-
sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name', [ @objtype = ] 'object_type' ]
Eg. - To rename a column of table – “MyTable” from “OldColumnName” to “NewColumnName”, use following syntax:-
EXEC sp_rename 'MyTable.[OldColumnName]', 'NewColumnName', 'COLUMN'
And yes, that’s it. Pretty simple, but then you need to know it that something like this exists. :)