In SQL, we can alter or change the name of the SQL database either by using ALTER DATABASE statement or SP_RENAMEDB statement or by using SQL Server Management Studio.
By using the “ALTER Database” statement, we can change the name of the 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 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” is the name of the present database and “newdbname” is the new name of the 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 the above queries, then refresh your SQL database section to check for the database with new name like as shown below.
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 the 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 the 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 the SQL server either by using the ALTER DATABASE, SP_RENAMEDB statements or directly with SQL Server Management Studio based on our requirements.