How to Rename Database or Table or Column in two ways


The article gives you detailed information with screen shots about renaming a Database, Table and Column in two ways. One way is using Sql Server Management Studio, with this you can rename easily. The other way is using SQL command, but for this you need to remember SQL Command syntax for renaming.

You can rename a Database or a Table or a Column using two ways
1. Using SQL Server Management Studio
2. Using Transact-SQL

To Rename a Database : Using SQL Server Management Studio
==========================================================
1. Open SQL Server and go to Object Explorer, choose database from the object explore

2. Now, right-click on the database name and choose Rename.

3. Type a new database name and press enter

Database Rename screenshot

To Rename a Database : Using Transact-SQL
============================================
1. Open SQL Server and open new Query window

2. Type the following command and execute it

Syntax :
Alter database [Old_Database_Name] modify Name = [New_Database_Name]
Ex :
Alter database ForTesting modify name = ForTesting_New

The above query will rename the databse 'ForTesting' with 'ForTesting_New'

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To Rename a Table : Using SQL Server Management Studio
==========================================================
1. Open SQL Server and go to Object Explorer, choose database from the object explore

2. Now, expand the tables node and choose the table which you want to rename.

3. Right-click on the Table name and choose Rename, type new table name and press enter

Renaming a Table

To Rename a Table : Using Transact-SQL
============================================
1. Open SQL Server and open new Query window

2. Type the following command and execute it

Syntax :
EXEC sp_rename [Existing_object name],[New Name],[Object Type]
Ex :
Use [ForTesting]
EXEC sp_rename
'dbo.MyOrders' -- Existing Table including Schema Name
,'MyOrders_New' -- New Table Name
,'Object' -- Use Object designation for a table

The above query will rename table 'MyOrders' with 'MyOrders_New' in the databse 'ForTesting'

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To Rename a Column : Using SQL Server Management Studio
==========================================================
1. Open SQL Server and go to Object Explorer, choose database from the object explore

2. Now, expand the tables node and then expand the columns node.

3. Choose the column name, you want to change. Right-click on it and choose Rename, type new column name and press enter

Column Renaming

To Rename a Table : Using Transact-SQL
============================================
1. Open SQL Server and open new Query window

2. Type the following command and execute it

Syntax :
EXEC sp_rename [Existing_object name],[New Name],[Object Type]
Ex :
Use [ForTesting]
EXEC sp_rename
'MyOrders.MyOrderAmount' -- Table name and Column name
'MyOrderAmount_New', -- New column name
'COLUMN' -- Object Type

The above query will rename column 'MyOrderAmout' with 'MyOrderAmout_New'

I hope this might help you


Attachments

Comments

Author: maruthanayakam21 Feb 2014 Member Level: Silver   Points : 6

Database Rename:

Option 1 - Rename SQL Server Database using T-SQL

This command works for SQL Server 2005, 2008, 2008R2 and 2012:

ALTER DATABASE oldName MODIFY NAME = newName

Option 2 - Rename SQL Database using SSMS

If you are using SQL Server Management Studio, right click on the database name and select the option "Rename". This did not exist in SQL Server 2000, but if you use Management Studio to manage SQL Server 2000 you can also take advantage of this option.

Table Rename:

Option 1 - Rename SQL Server Database Table using T-SQL

In SQL Server, one cannot use SQL to rename a table. Instead, it is necessary to use the sp_rename stored procedure to do so. The syntax is:
sp_rename ‘OLD_TABLE_NAME’, ‘NEW_TABLE_NAME’
Note the single quote above. Using a double quote or no quote will both result in an error. To rename a table from Top_Scores to Best_Scores, we use the following:
sp_rename ‘Top_Scores’, ‘Best_Scores’;

Option 2 - Rename SQL Database Table using SSMS

If you are using SQL Server Management Studio, right click on the database name and select the option "Rename". This did not exist in SQL Server 2000, but if you use Management Studio to manage SQL Server 2000 you can also take advantage of this option.

Calumn Rename:

Option 1 - Rename SQL Server Database Table using T-SQL

In SQL Server, one cannot use SQL to rename a table. Instead, it is necessary to use the sp_rename stored procedure to do so. The syntax is:

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

Option 2 - Rename SQL Database Table using SSMS

If you are using SQL Server Management Studio, right click on the database name and select the option "Rename". This did not exist in SQL Server 2000, but if you use Management Studio to manage SQL Server 2000 you can also take advantage of this option.

Regards,
Marudhu...

Author: Mohite Mina20 Mar 2014 Member Level: Silver   Points : 0

sp_rename tblProgram, tblProgram1

Author: Mohite Mina20 Mar 2014 Member Level: Silver   Points : 0

for table

sp_rename tblProgram, tblProgram1

for database

ALTER DATABASE TESTING
Modify Name = TESTING1 ;


To rename a database

In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

Make sure that no one is using the database, and then set the database to single-user mode.

Expand Databases, right-click the database to rename, and then click Rename.

Enter the new database name, and then click OK.

Author: Mohite Mina20 Mar 2014 Member Level: Silver   Points : 3

Using SQL Server Management Studio
To rename a database

In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

Make sure that no one is using the database, and then set the database to single-user mode.

Expand Databases, right-click the database to rename, and then click Rename.

Enter the new database name, and then click OK.

sp_rename tblProgram, tblProgram1

ALTER DATABASE TESTING
Modify Name = TESTING1 ;



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: