CREATE, ALTER, AND DROP DATABASE IN SQL SERVER

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.

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.

SQL server database

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.

Create new database

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.

SQL new database name

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.

SQL new user database

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.

Alter database name

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.

Rename database in SQL

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.

Delete database

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

thirteen + 3 =