sp_rename in MS 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)

http://msdn.microsoft.com/en-us/library/ms188351.aspx

Tagged with: ,