You can rename a database using ALTER DATABASE statement.
Limitations and restrictions of renaming a database
1. Database cannot be renamed to an existing database name. As every database name must be unique so the new name should be unique and should not already exists in the database management system.
2. System databases cannot be renamed. The Alter Database statement cannot be used to rename system databases. These databases are used for system internal working so the database systems doesn’t allow altering such databases.
3. You cannot alter the database that is already in use. If a user is accessing the database, other user cannot alter the database until unless the previous user done accessing the database.
4. The user must have ALTER permission to rename the database.
Syntax of ALTER DATABASE Statement
ALTER DATABASE oldDBName MODIFY NAME = newDBName
Here oldDBName
refers to the name of the existing database and newDBName
refers to the new name.
Renaming Database Example:
Let’s say you want to change the database name “SalesData” to “Sales”. You can do so by using the following command:
ALTER DATABASE SalesData MODIFY NAME = Sales;
This statement will change the database name from "SalesData"
to "Sales"
. All the tables, data, schema and other data associated with the database remains unchanged.
To ensure the successful execution of the query, you must ensure that the database “Sales” doesn’t exist prior to execution the above statement. If any database with the name “Sales” is already present then the above statement will throw an error and the renaming will not be successful.
Example 2: Let’s say, you want to change the database that contains the profit and loss data of company, from the name "Profit"
to "ProfitLoss"
.
ALTER DATABASE Profit MODIFY NAME = ProfitLoss;
This statement will change the database name from "Profit"
to "ProfitLoss"
in the database system.