Summary -

In this topic, we described about the below sections -

Why to rename the database?

A database always created for purpose either it may be as per requirement or it may be created while we are learning. If the database is created wrongly or by mistakenly with some other name, it can be renamed without deleting it.

There are different statements for different types of server. We are checking information for MySQL and SQL Server -

MySQL - How to rename the database?

RENAME statement used to fulfils the renaming of the database in MySQL.

The syntax for renaming the database is -

Rename Database

Element Description Restrictions
old-database-name Specifies the old database name. Should be unique name among the names of the databases in the server.
new-database-name Specifies the new database name. Should be unique name among the names of the databases in the server.
owner Specifies owner of the old database. Should be the owner of the database.

Points to Note -

You can rename a database only when the following are true -

  • You created the database.
  • You have the DBA privilege on the database.

Example -

Consider we are going to rename the existing database named emptdb. The code for renaming the database is - 

RENAME DATABSE emptdb MODIFY NAME=emptestdb;

After executing the above query, emptdb gets renamed to emptestdb. As we discussed earlier, we must have admin privileges to rename the database.

SQL Server -How to rename the database?

ALTER DATABASE statement fulfils renaming of the database in SQL server 2005, 2008, 2008R2 and 2012.

The syntax for renaming the database is-

ALTER DATABASE current_name MODIFY NAME=new_name;
  • current_name - Specifies the current name of the database.
  • new_name - Specifies the new name of the database.

Points to Note -

There are some most important points to keep in mind before executing ALTER DATABASE are -

  • Before renaming the database, make sure that it was communicated to all the stakeholders and close connections that are connecting to the database.
  • In real-time project, as a developer we do not have access to delete the database. There is separate team called database admin team to perform all DDL transactions.

Example -

Consider we are going to rename the existing database named emptdb. The code for renaming the database is -

ALTER DATABSE emptdb MODIFY NAME=emptestdb;

After executing the above query, emptdb gets renamed to emptestdb. As we discussed earlier, we must have admin privileges to rename the database.

Verify the Database

Once the database is successfully renamed, we can verify the database renamed or not by using below statement -

SHOW DATABASES [LIKE pattern];
  • pattern - Specifies the partial string of the database name.

Example-

Consider above example, we can verify the database by using the below query -

SHOW  DATABASES  LIKE "emp%";

Once the above query executed, it displays the results like below -

+-------------------+
| Database           |
+-------------------+
| emptestdb          |
+-------------------+
1 rows in set (0.00 sec)

Note :- Before goin g to rename-– the database, it is always suggestable to verify the database is existing or not.

What happen if try to rename the nonexisting database?

When trying to rename a database that does not exist, system throws the error: "Can't rename database 'database_name'; database doesn't exist."