SQL Alter Database Statement

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.

SQL ALTER Database Statement

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

SQL SP_RENAMEDB Statement

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.

SQL Alter Database Examples

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

Example1

Following is the example to alter 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 above queries, then refresh your sql database section to check for database with new name like as shown following.

 

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

 

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.

 

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

 

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

 

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

 

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.

Topics Covered