SQL Alter Database Statement

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.

SQL ALTER Database Statement

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

SQL SP_RENAMEDB Statement

In SQL, we have another way to change the name of the database, i.e., by using the 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 database's new name.

SQL Alter Database Examples

The following SQL statements will rename or change the database name from “sqltutorialexamples” to “sqltutorialexample”. 

Example1

Following is the example of altering database name using ALTER DATABASE statement in SQL server.

  

ALTER DATABASE sqltutorialexamples MODIFY NAME= sqltutorialexample

Example2

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 a new name, as shown below.

 

After renaming sql database using alter and rename statements in sql server

SQL Server Management Studio Alter Database

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.

 

to change database name in sql server right click on respective database and select rename

 

If you get any problem renaming a database using SQL management studio, that problem might be because of another process or an application using the database with the old name. To fix this problem, you need to restart the SQL Server running service, and 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 shown below.

 

Restart sql server service in services section to rename database in sql server

 

 Once you restart the service, right-click on your database à select Rename option, and change the name. Once we are done with the changes, that will be like as shown below. Here we renamed a database “sqltutorialexamples” to “sqltutorialexample”.

 

After renaming sql database using alter and rename statements in sql server

 

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.

Previous
 
Next