A lot of times we need a change . While working on an existing database, we may need to change the database name and in some cases want to rename existing database objects. This can be done in a few seconds.
In SQL Server this can be done in this manner :
1. Renaming a database :
The SQL Server command for renaming a database is :
Syntax: EXEC sp_renamedb ‘oldName’, ‘newName’ ;
eg: Suppose we have a database named “GPSTrainees” and we want to rename it to “KLMSTrainees“
We can write :
EXEC sp_renamedb ‘GPSTrainees’ , ‘KLMSTrainees’ ;
However, though this command works on SQL Server 2000 and SQL Server 2005, it will not be supported in future versions of SQL Server. The new command that should be used for SQL Server 2005 and beyond is:
ALTER DATABASE oldName MODIFY NAME = newName ;
eg: ALTER DATABASE GPSTrainees MODIFY NAME=KLMSTrainees ;
2. Renaming an object :
For renaming an object like table,stored procedure,triggers we will be using the following command.
eg: Suppose we want to rename a table from dipali_Autoincrementto Deepali_Autoincrement
sp_rename ‘dipali_Autoincrement’, ‘Deepali_Autoincrement’, ‘OBJECT’ ;
Renaming a column :
Suppose we want to rename a column Full_Name in table dipali_Library to Name.Then we can write:
sp_rename ‘dbo.dipali_Library.Full_Name’, ‘Name’, ‘COLUMN’ ;
Renaming Database Owner :
Change the current database owner to DBadmin
eg: sp_changedbowner ‘DBadmin’ ;
When SQL Server Management Studio is used to make changes,the sp_rename stored procedure is called by the GUI .