How to Create Database in SQL Server

A database is a storage location where we can store our business data. The database uses tables to store our information in a normalizes way so that, We can easily Select, Update and Delete the business data. In this article, we will show you, How to Create Database in SQL Server, Rename SQL Database and Delete Database in SQL with an example.

How to Create Database in SQL Server example

Before we start create new database in SQL Server, Let us see the list of available databases. Below screenshot will show you the list of databases available in the current instance.

The basic syntax for SQL create Database is:

CREATE DATABASE Database_Name

In this example, we are going to create a new database called New_Database. So, Replace the Database_Name with New_database in SQL query window as shown below

-- Code for SQL Create Database
CREATE DATABASE New_Database;

Click on the Execute button to execute the create database command

From the below screenshot you can observe that Command is executed successfully and you can see the New_Database in our object explorer

TIP: If you didn’t find the newly created database, Please click on the refresh button in object explorer

Let us see what will happen When we execute the same command again. From the below screenshot you can observe that it is throwing an error saying: New_database already exists. Choose a different database name.

NOTE: In an organization, we may or may not have the privileges to know the available databases SQL Server. So, it is always advisable to check whether the database name already exists or not

How to Check whether SQL Database name exists or not

This can be done in two ways:

The following statement will only execute Create Database Statement if the New_database in not available in the system database

-- SQL Create Database Example
IF NOT EXISTS 
   (
     SELECT name FROM master.dbo.sysdatabases 
     WHERE name = N'New_Database'
    )
CREATE DATABASE [New_Database]

We just replaced the If Not Exists with If Exists and added select statement to display the message. Steps involved in the Following statement are:

• If the New_database already exists then the following query will display a message saying database already exists
• Create database only execute if the New_database in not available in a system database

-- Code to Sql Server Create Database
IF EXISTS 
   (
     SELECT name FROM master.dbo.sysdatabases 
    WHERE name = N'New_Database'
    )
BEGIN
    SELECT 'Database Name already Exist' AS Message
END
ELSE
BEGIN
    CREATE DATABASE [New_Database]
    SELECT 'New Database is Created'
END

ANALYSIS
The following statement will check for the database name New_database in the system database.

 SELECT name FROM master.dbo.sysdatabases 
     WHERE name = N'New_Database'

If the database does not exist, then only following create statement will be executed

CREATE DATABASE [New_Database]

otherwise, below command is executed. It’s going to display a message saying that the database already exists

SELECT 'Database Name already Exist' AS Message

OUTPUT

How to Create Database in SQL Server in Management Studio

First, open the SQL Server Management Studio. Within the object explorer, Right click on the Databases folder will open the context menu. Please select New database.. option from the context menu as shown below

Once you select New database.. option, following window will be opened. Here we left Owner as default and database name as New_database as shown below. Click OK to create a new database

Let us see what will happen When we create the database with an existing name. From the below screenshot you can observe that it is throwing an error

How to Delete Database in SQL Server

To Delete database in SQL server, we can simply use the following syntax

The syntax for Deleting database or Dropping Database in SQL server is:

DROP DATABASE [Database Name]

In this example we are going to delete New_Database So, within the SQL query window, Please write the following query

DROP DATABASE [New_Database]

Let us see, What will happen when we execute the same command once again:

From the above screenshot, you can observe that it is throwing an error saying: New_database doesn’t exist.

The better approach is to check whether the database name exists or not because sometimes, your college or your team leader may delete the database which you are trying to delete.

IF EXISTS 
   (
     SELECT name FROM master.dbo.sysdatabases 
     WHERE name = N'New_Database'
    )
DROP DATABASE [New_Database]

ANALYSIS
The following statement will check for the database name New_database in the system database.

 SELECT name FROM master.dbo.sysdatabases 
     WHERE name = N'New_Database'

If the database exists, then only following drop statement will be executed

DROP DATABASE [New_Database]

OUTPUT

How to Rename Database in SQL Server

To rename database in SQL server, we can simply use the system stored procedure sp_renamedb

The syntax to rename SQL database is:

SP_RENAMEDB [Old Database Name],[New Database Name]

In this example we are going to rename New_Database with New_Db So, within the SQL query window, Please write the following query

SP_RENAMEDB  [New_Database],[New_Db]