In sql, we can alter or change the name of SQL database either by using ALTER DATABASE statement or SP_RENAMEDB statement or by using SQL Server Management Studio.
By using “ALTER Database” statement, we can change name of SQL database. Following is the syntax to alter or change sql database using ALTER Database statement
ALTER DATABASE olddbname MODIFY NAME= newdbname
In sql, we have an another way to change the name of database i.e, by using SP_RENAMEDB statement. Following is the syntax using EXEC sp_renamedb statement to change or rename a sql database.
EXEC sp_renamedb'olddbname', 'newdbname'
Here “olddbname” means name of the present database and “newdbname” means its a new name for database.
The following SQL statements will rename or change database name from “sqltutorialexamples” to “sqltutorialexample”.
Following is the example to alter database name using ALTER DATABASE statement in sql server
ALTER DATABASE sqltutorialexamples MODIFY NAME= sqltutorialexample
Following is the example rename or change database name using EXEC sp_renamedb statement in sql server
EXEC sp_renamedb'sqltutorialexamples', 'sqltutorialexample'
Once you execute either of above queries, then refresh your sql database section to check for database with new name like as shown following.
To change the database name, open SQL Server Management Studio à Select your database in Databases section à Right click and Select Rename like as shown below .
In case, if you get any problem to rename a database using SQL management studio then that problem might be because of another process or an application is using database with the old name. To fix this problem you need to restart the SQL Server running service, then it will abort all the applications or services which are using current database.
To restart the service, go to Services à Select SQL Server à Right click Restart service like as shown following.
Once you restart the service, now right click on your database à select Rename option and change the name. Once we are done with the changes, then that will be like as shown below. Here we renamed a database “sqltutorialexamples” to “sqltutorialexample”.
This is how we can rename or alter the database name in sql server either by using ALTER DATABASE, SP_RENAMEDB statements or directly with SQL Server Management Studio based on our requirements.