11 January 2012

SQL: Rename Column/Table Name

Hi All,

We all sometimes need to rename a column/table from our database, however we do not want to drop all our records from the table where the column is renamed or the name of that table is changed.

For that there is such a nice SPROC in MS SQL: sp_rename.

This SPROC can be used to rename any user-created object in current DB.

However there is also a caution:

Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.

That means that you have to take care of all the other objects which has a reference to the object that you are renaming because otherwise it will throw an error.

I will give an example of how you rename a column:

EXECUTE sp_rename N'dbo.myTable.initial_column_name', N'new_column_name', 'COLUMN'
For more information go to microsoft's page: http://msdn.microsoft.com/en-us/library/ms188351.aspx

Thanks for visiting my blog,

No comments:

Post a Comment

your thoughts are welcome:

Need more? Leave comments and subscribe to my blog.