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. :)
1 comment:
Though such a feature exists it should not be advised to use it for one simple reason that it will add cost to the customer in doing an impact analysis of the impact of various programs accessing the table and if there are some reference rewriting the existing code .. creating synonyms or having a staging table where data is populated on UPSERT in the master table are cheaper options for the customer .. just my view ..
Post a Comment