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
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
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
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
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...