SQL - Rename Database

---

There can be several reasons to rename a database name. One of the reasons could be to avoid naming conflicts or to separate different types of data into different databases. Another reason can be to arrange them in an organized way that can make them more descriptive and easier to identify.

• SQL provides a simple ALTER DATABASE…MODIFY command to rename an existing database.

• SQL Server also provides a built-in stored procedure called sp_renamedb, which can be used to rename a database name.

Note − If you have decided to rename a database, make sure there are no active transactions where the old database name is being used, otherwise the complete operation might halt once you rename the database.

ALTER DATABASE…MODIFY Query

The ALTER DATABASE…MODIFY query in SQL is used to rename the name of an existing user-created database in an SQL Server.

Syntax

Following is the syntax of the ALTER DATABASE…MODIFY command −

ALTER DATABASE existing_database_name MODIFY NAME = new_database_name;

Example

Before renaming the database, let us list all the databases present in the SQL server. To perform that we need to use the following query −

SQL> SELECT name From sys.databases

Following are the databases that exist in the SQL server −

+-------------+
|     name    | 
+-------------+
| master      |
| tempdb      |
| model       |
| msdb        |
+-------------+

Here, the master, tempdb, model, and msdb are the databases that are created automatically at the time of SQL server installation.

Now, let us create a new database on the SQL server using the query below −

SQL> CREATE DATABASE testDB;

As we can see, the database has been created.

+-------------+
|     name    | 
+-------------+
| master      |
| tempdb      |
| model       |
| msdb        |
| tempDB      |
+-------------+

Now, let us try to rename the user-created database named “testDB” to “tutorialsDB” using the ALTER DATABASE…MODIFY query.

SQL> ALTER DATABASE testDB MODIFY NAME = tutorialsDB;

Output

When we execute the above query, the output is obtained as follows −

The database name 'tutorialsDB' has been set.

Note − Before executing the above query, you need to make sure that the database "testDB" is present in the current database server. Else, the above query will return an error.

Verification

Let us verify whether the database's name has been changed or not using the following query −

SQL> SELECT name From sys.databases;

The above query lists all the names of the databases and we can see that the name of the database has changed.

+-------------+
|     name    | 
+-------------+
| master      |
| tempdb      |
| model       |
| msdb        |
| tutorialsDB |
+-------------+

Renaming database using sp_renamedb

Another way to rename the existing user-created database in an SQL server is by using the stored procedure.

Syntax

Following is the syntax of the stored procedure sp_renamedb −

EXEC sp_renamedb 'existing_database_name', 'new_database_name' 

Example

Let us try to rename the user-created database “tutorialsDB” to “testDB” using the following query.

SQL> Exec sp_renamedb 'tutorialsDB', 'testDB';

Output

When we execute the above query, the output is obtained as follows −

The database name 'tutorialsDB' has been set.

Verification

Let us verify whether the database's name has been changed or not using the following query −

SQL> SELECT name From sys.databases

The above query lists all the names of the databases and we can see that the name of the database has changed.

+-------------+
|     name    | 
+-------------+
| master      |
| tempdb      |
| model       |
| msdb        |
| testDB      |
+-------------+

Renaming a System Database

While we install the SQL server, some databases are automatically created and those are called “System Databases”.

Example

Let us try to rename the system-created database “master” to “student” using the query below −

SQL> ALTER DATABASE master MODIFY NAME = student;

Error

As we can see in the output below, the above query returned an error because we cannot rename the system-created database.

Cannot change the name of the system database master.