.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

sp_rename in MS SQL Server

Posted By:Dhiraj Ranka       Posted Date: July 25, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

sp_rename in MS SQL Server. In this article I am going to demonstrate the use of "sp_rename" stored procedure. It is used to rename any user created objects in SQL Server.
 

Background

When ever we have to change anything in the database we mostly use Management Studio and do the changes. But its always handy to know some useful stored procedure and commands for this kind of stuff. In this article I am going to demonstrate the use of "sp_rename" stored procedure. It is used to rename any user created objects in SQL Server.

 

Let's Go

When we say rename then this object can be anything, it can be table, column, index, alias, data type or any user defined data type in Microsoft .NET Framework CLR. But using this there can several issues like, our existing scripts and stored procedure will fail to run as it will throw an error for used object(s) as they got renamed. It is advised to use this stored procedure for renaming the stored procedures, triggers, user-defined functions or views, rather than dropping and recreating that object. 

Usage 
sp_rename '[old_object_name]', '[new_object_name]', '[object_type]' 

Arguments 
old_object_name - This is a qualified or unqualified name of the user created object. If we want to rename a column of a table then it should be specified as 'table.column' or 'schema.table.column' and same goes for index. It does not have any default value, so we have to specify some value in the quotation. 

new_object_name -Its a new name of the object which should be in a single part and must follow the rules for identifiers. It also does have any default value. 

object_type - This is type of object being renamed. Default value of it is NULL and it can be any of the followings:

 
Value Description
COLUMN A column to be renamed.
DATABASE A user-defined database. This object type is required when renaming a database.
INDEX A user-defined index.
OBJECT An item of a type tracked in sys.objects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, and rules.
USERDATATYPE An alias data type or CLR User-defined Types added by executing CREATE TYPE or sp_addtype
Its return type is integer and on success it returns 0 (zero) and non zero on failure.

Examples

1. Renaming a table. In following example Employee table from HR schema is renamed to Emp_Master in Sample database.
USE Sample;
GO
EXEC sp_rename 'HR.Employee', 'Emp_Master';
GO
2. Renaming a column. In following example EmployeeID column of Employee table from HR schema is renamed to EmpID in Sample database.
USE Sample;
GO
EXEC sp_rename 'HR.Employee.EmployeeID', 'EmpID', 'COLUMN';
GO

Reference

sp_rename (Transact-SQL) 
 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend