How to Change Database Name in SQL Server using Query?

by

In this SQL Server tutorial, you will learn about how to change database name in SQL Server using query.

You will understand the syntax that you can use to change the name of the database. Also, you will learn how to view all the databases on the SQL Server.

Finally, you will see how to change the name of the database using the example.

Change Database Name in SQL Server Using Query

To change the database name in SQL Server using a query, you can use the following syntax:

ALTER DATABASE old_database_name MODIFY NAME = new_database_name;

Where,

  • ALTER DATABASE: It is the command to change the properties of the database.
  • old_database_name: It is the old or current database’s name with the actual name you want to change.
  • MODIFY NAME: It is the statement that means change the name of the property of the database. This means modifying the name of the old databases with a new name.
  • new_database_name: It is the new name for the old database that you want to assign.

But before changing the database name, let’s know about ‘why to change the database name?’, there can be several reasons behind changing the database name.

  • As time passes, database names become unclear or similar to other databases. Giving more descriptive and meaningful names to the database helps in preventing confusion and mistakes in maintenance or querying.
  • Sometimes, you may required to hide sensitive information related to the database, such as its purpose with a particular project. In that kind of case, changing the name of the database protects data.
  • Due to naming conventions, you may need to change the name of the database.

Now, to change the database name in SQL Server, open the command prompt and run the below command to connect to the SQL Server instance.

sqlcmd -S MSI\SQLEXPRESS

Here, connecting to the SQL Server instance named MSI\SQLEXPRESS, the SQL Server instance name can be different on your system. It depends on how you have configured your SQL Server.

  • If you want to know the SQL Server instance quickly, then open the SQL Server Management Studio, and if you have configured the SQL Server instance, then you will see the name of your SQL Server instance.

Then, view the database name that you want to change to a different name using the below query.

SELECT name FROM sys.databases;
GO

Here in the above query, sys.databases is called system view in SQL server that stores information about all the databases on the SQL Server.

When the above query is executed, it lists all the database names from the sys.database view and shows in the result set. It shows the system database, such as master, tempdb, model, msdb with a user-created database.

Change Database Name in SQL Server Using Query View Existing Databases

The above query lists all the databases that you can see in the above picture. Here, suppose you want to change the database ‘Orders’ to more descriptive names such as ‘Product_Orders’, which indicates this database order is related to the products.

To change the name of the database ‘Orders’ to ‘Product_Orders’ use the below query in your command line.

ALTER DATABASE Orders MODIFY NAME = Product_Orders;

View the databases using the below query.

SELECT name FROM sys.databases;
GO
Change Database Name in SQL Server Using Query

After executing the above query, you can see the database name changed from Orders to Product_Orders. This is how to change the database name in SQL Server using the query.

Conclusion

In this SQL Server tutorial, you learned how to change the database name in SQL Server using a query. Also learned how to view all the existing databases on the SQL Server instance. At the end, you saw how to change the name of the database using an example.

You may also like: