How to Create Database in SQL Server
Database is a storage location where we can store our business data. 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, Renaming Database and Deleting Database in SQL with example.
How to Create Database in SQL Server 2014 with example
Before we start creating new database, Let us see the available databases. Below screenshot will show you the databases available in current instance.
SQL Syntax to create Database is:
1
|
CREATE DATABASE Database_Name
|
In this example, we are going to create new database called New_Database. So, Replace the Database_Name with New_database in SQL query window as shown below
1
|
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 error saying: New_database already exists. Choose different database name.
NOTE: In an organization, we may or may not have the privileges to know the available databases so, it is always advisable to check whether the database name already exists or not
How to Check whether Database name exists or not
This can be done in two ways:
Following statement will only execute Create Database Statement, if the New_database in not available in system database
SQL CODE
1
2
3
4
5
6
|
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 following query will display a message saying database already exists
- Create database only execute if the New_database in not available in system database
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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
Following statement will check for the database name New_database in system database.
1
2
|
SELECT name FROM master.dbo.sysdatabases
WHERE name = N'New_Database'
|
If the database does not exists, then only following create statement will be executed
1
|
CREATE DATABASE [New_Database]
|
otherwise, following command will be executed. It’s going to display a message saying that the database already exists
1
|
SELECT 'Database Name already Exist' AS Message
|
OUTPUT
How to Create Database in SQL Server using GUI
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 new database
Let us see what will happen, When we create the database with existing name. From the below screenshot you can observe that, it is throwing error
How to Delete Database in SQL Server
To Delete database in SQL server, we can simple use the following syntax
Syntax for Deleting database or Dropping Database in SQL server is:
1
|
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
1
|
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 exists.
Better approach is check whether the database name exists or not because sometimes, your college or your team leader may deleted the database which you are trying to delete.
1
2
3
4
5
6
|
IF EXISTS
(
SELECT name FROM master.dbo.sysdatabases
WHERE name = N'New_Database'
)
DROP DATABASE [New_Database]
|
ANALYSIS
Following statement will check for the database name New_database in system database.
1
2
|
SELECT name FROM master.dbo.sysdatabases
WHERE name = N'New_Database'
|
If the database exists, then only following drop statement will be executed
1
|
DROP DATABASE [New_Database]
|
OUTPUT
How to Rename Database in SQL Server 2014
To rename database in SQL server, we can simple use the system stored proceduresp_renamedb
Syntax for renaming database in SQL server is:
1
|
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
1
|
SP_RENAMEDB [New_Database],[New_Db]
|
Thank You for Visiting Our Blog
