To rename an object in SQL Server you can use sp_rename system stored procedure or you can do this via Object Explorer in SQL Server Management Studio.
Method 1: To rename an object using SQL Server Management Studio:
Step 1. Right Click on the object in Object Explorer and select "Rename"
Step 2. Specify new name and press Enter.
You can rename any object using object in Object Explorer.
Method 2: Renaming an object using sp_rename:
You can also rename using sp_rename system stored procedure.
sp_rename takes below arguments:
| Parameter | Description |
| @objname | Object Name. When renaming a column you need to specify table name.column name optionally you can also prefix schema name |
| @newname | New name for the specified object |
| @objtype | Type of the object. You can rename below objects using sp_rename: COLUMN DATABASE INDEX OBJECT STATISTICS USERDATATYPE Default value for this parameter is TABLE |
Following example demonstrates how you can use sp_rename to rename table and column names:
Let’s create a Test Table to work with:
USE [SqlAndMe]
GO
CREATE TABLE tblProduct
(
ID INT,
pName NVARCHAR(50)
)
GO
Now we can rename the Table Name and column names as below:
– Rename table tblProduct to Table_Products
EXEC sp_rename 'tblProduct', 'Table_Products'
GO
– Rename Column ID to ProductID
EXEC sp_rename 'Table_Products.ID', 'ProductID', 'COLUMN'
GO
– Rename Column pName to ProductName
EXEC sp_rename 'Table_Products.pName', 'ProductName', 'COLUMN'
GO
Result Set:
Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
You can verify that the changes have been made by issuing a SELECT against the table using new table name. SQL Server is also nice enough to warn us that renaming an object can break scripts and stored procedures. What this means is if you have used table/column names in any script/stored procedure it will not be updated automatically, you will need to go through your scripts/stored procedures manually and update them accordingly.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Filed under: Management Studio, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data 
