CREATE, ALTER, AND DROP DATABASE IN SQL SERVER

Last updated on April 23rd, 2020 at 08:57 am

In this article, we will discuss how to Create, Alter, and Drop a database in SQL Server.
In my previous article, we have discussed all about the Types of SQL Commands.

Table of Contents

1 Database Introduction
2 Create Database In SQL Server
- 2.1 CREATE DATABASE Statement In SQL Server
- 2.2 Create Database using SQL Server Management Studio(SSMS)
3 How to Alter a database in SQL Server?
- 3.1 Alter Database Using T-SQL Command
- 3.2 Rename Database Using SQL Server Management Studio
4 How to Delete a database in SQL Server?
- 4.1 DROP Database Using T-SQL Command
- 4.2 DROP Database Using SQL Server Management Studio
5 Conclusion
- 5.1 Related

Database Introduction

Database : In the SQL server a database is a place to store the data in an electronic format. It is an organized collection of database objects such as tables, views, functions, stored procedures, triggers and so on.
It allows a user to easily access or manipulate the data.

Create Database In SQL Server

In SQL Server, there are two ways to create a user-defined database, either by using the Transact-SQL statements or by using SQL Server Management Studio.

CREATE DATABASE Statement In SQL Server

In SQL Server the CREATE DATABASE statement is used to create a new user-defined database.

Following is the syntax for creating a SQL Server database.

CREATE DATABASE DatabaseName;

Example to create a new database “SampleDB” in the SQL server.

CREATE DATABASE SampleDB;

Once we run the above SQL statement, it will create a new database called “SampleDB” in the SQL server.

Create Database using SQL Server Management Studio(SSMS)

Step 1: Right Click on the Database folder from the Object Explorer window and then select the ‘New Database’ option shown in the below image.

Step 2: A new database dialog box will appear, enter the new Database name and click on the OK button as shown in the below image.

Step 3: Once we clicked on the OK button on the above dialog box, it will create a new database “SampleDB” as shown in the below image.

How to Alter a database in SQL Server?

After the creation of a new database, we can rename the database either by Transact-SQL command or by using SQL Server Management Studio.

Alter Database Using T-SQL Command

Now Let’s Rename the Database using the T-SQL ALTER DATABASE command or by using the system-defined stored procedure SP_RENAMEDB .

Following is the syntax.

ALTER DATABASE OldDatabaseName MODIFY NAME = NewDatabaseName ;

-- OR

EXECUTE SP_RENAMEDB 'OldDatabaseName','NewDatabaseName';

Following is the example to rename a database in SQL Server.

-- Using ALTER command
ALTER DATABASE SampleDB MODIFY NAME = MyDatabase;

-- OR

-- Using system-defined stored procedure

EXECUTE SP_RENAMEDB 'SampleDB','MyDatabase';

After executing the above SQL commands, refresh the “Databases” folder in the “Object Explorer” window to see the new database name as shown in the below image.

Rename Database Using SQL Server Management Studio

Now Let’s rename the database from “MyDatabase” to “SampleDB” using the SQL server management studio.

To rename the database select the database from the “Databases” folder > Right-click and select the Rename option as shown in the below image.

Note: Before rename, Always make sure that the database is not in use otherwise it will not allow you to change the name of the database.

How to Delete a database in SQL Server?

In SQL Server, we can delete or drop a database in two ways, either by using the T-SQL DROP DATABASE statement or by SQL Server Management Studio (SSMS).

DROP Database Using T-SQL Command

Following is the syntax to drop a database.

DROP DATABASE DatabaseName ;

Following is the example to delete the “SampleDB” database.

DROP DATABASE SampleDB ;

DROP Database Using SQL Server Management Studio

To delete a database, select the database from the “Databases” folder > Right-click and select the Delete option as shown in the below image.

When we delete a database either by T-SQL command or by SSMS, two files called LDF and MDF are deleted internally.

Conclusion

We can CREATE, ALTER, and DROP a database either by Transact-SQL command or by using SQL Server Management Studio (SSMS).

We can only delete user database, not the system-defined database such as master , model , msdb , and tempdb .

Hope you enjoyed this post. Thanks for visiting.

Author
Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.