Home > Management Studio, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Working With Data > SQL Server – How to Rename Table/Column in SQL Server

SQL Server – How to Rename Table/Column in SQL Server

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"

image

Step 2. Specify new name and press Enter.

image

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

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 271 other followers

%d bloggers like this: