How To Create Alter and Drop Database in SQL

A SQL Server database can be created using two ways.

1. Using a Query (T-SQL)

2. Using SSMS GUI(Graphical User Interface)

Create Database Using T-SQL

The syntax to create the database using query is:

CREATE DATABASE DatabaseName;
GO

Example: We create a database named SampleDB using query as:

CREATE DATABASE SampleDB;
GO

Whether, we create a database using T-SQL or by using SQL Server Graphical User Interface (GUI) the following two files gets created.
.MDF file- Data File( which contains actual data)
.LDF file-Transaction Log file (which is used to recover database).

Alter Database Using T-SQL

To alter the database, the syntax to alter the database is:

ALTER DATABASE DatabaseName MODIFY Name = NewDatabaseName;
GO

Example: We alter the database named SampleDB to SampleDatabase using query as:

ALTER DATABASE SampleDB MODIFY Name = SampleDatabase;
GO

Alternatively, you can also use system stored procedure to alter the database name.
Syntax to alter the database name is:

EXECUTE sp_renameDB  'OldDatabaseName', 'NewDatabaseName';

Example: We alter the database SampleDatabase to SampleDB using query as:

EXECUTE sp_renameDB  'SampleDatabase', 'SampleDB';

DELETE/DROP Database Using T-SQL

To delete or drop a database, we write the query as:

DROP DATABASE DatabaseName;
GO

Example: We drop the database using query as:

DROP DATABASE SampleDB;
GO

When you drop a database, both the .MDF and .LDF files gets deleted.

You cannot drop a database, if it is currently in use. When you try to delete such database which is in use, you get an error

“Cannot drop database DatabaseName because it is currently in use”.

If the other users are connected to your database, you need to put the database in single user mode and then drop the database.

ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

With Rollback immediate option, will roll back all incomplete transactions and closes the connection to the database.

NOTE: System Database cannot be dropped because these are used as the template for all databases created on the instance of SQL Server.

Create Database Using SSMS GUI

We create database using SQL Server Management Studio GUI (Graphical User Interface) step-by-step as:

Step-I: Open SQL Server Management Studio and Click on Connect button as shown in the following figure.

Step-II: Right-click on the Databases node and Click on New Database window from the pop-up window.

Step-III: By clicking on New Database from pop-up window, a new window will open as shown below.

In New Database window, select General from left pane.
Write Database name.
Select Owner of database.
From Database Files tab, there are Logical Name of database which consists of two files, one is the data file name and other is the log file name. Files types, file groups, Initial Size, Autogrowth, and Path of the database where database will store.
Click on OK button, if you want to create database on the default location. Else, if you want to change the database path, follow the Step-IV.

Step-IV: To change the database path other than default location, follow these steps:

Select General from Select a page tab.
Move the scroll bar to the right as shown in the figure in point 2.
Click on the […] of data and log files and select the path where you want to store the database.
Click on OK button. Database will be successfully created.

Alter Database Using SSMS GUI

To alter database using SQL Server Management Studio (SSMS) Graphical User Interface (GUI), follow these steps:

Step-I: Right-Click on database and click on Rename.

Step-II: Type the new name of database and press on Enter Key.

Database name will be changed as shown in the following image.

DELETE/DROP a Database Using SSMS GUI

To drop database using SQL Server Management Studio (SSMS) Graphical User Interface (GUI) follow the following steps.

Step-I: Expand Databases node in SQL Server Management Studio Object Explorer.

Step-II: Right-Click on the database which you want to delete.

Step-III: Click on Delete button from pop-up window as shown below.

Step-IV. A new window will open as shown below. Where

General option is already selected from Select a page tab.
Confirm the database which you are going to delete from Object to be deleted tab.
There are two checkboxes.

- Delete backup and restore history information for databases checkbox will delete the backup and restore history information along with database data and log files.
- Close existing connections checkbox will close all the connections before deletion.

4. Click on OK button.