Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

How to Create Database in SQL Server

by suresh

A database in Sql Server is a storage location where we can store our business data. The SQL database uses tables to store our information in a normalizes way. So by creating database in Sql Server, We can easily Select, Update and Delete the business data.

Let us see how to Create Database in SQL Server, Rename Database in SQL, and Delete Database in Sql Server with an example of each.

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. The list of available databases in Sql Server current instance are.

How to Create Database in SQL Server 1

The syntax for SQL create Database is:

CREATE DATABASE Database_Name

In this example, we create a new database in Sql Server called New_Database. So, Replace the Database_Name with New_database in SQL Server query window.

-- Code for SQL Create Database
CREATE DATABASE New_Database;

Click on the Execute button to execute the create database command

How to Create Database in SQL Server 2

The SQL Create Databse Command is executed successfully and you can see the New_Database in our object explorer. If you didn’t find the newly created database in Sql Server Object explorer, Please click on the refresh button

How to Create Database in SQL Server 3

Let us see, what will happen, When we execute the same SQL Create Database command again. It is throwing an error saying: New_database already exists. Choose a different database name.

How to Create Database in SQL Server 4

How to Check whether SQL Database name exists or not

In an organization, we may or may not have the privileges to know the available databases in SQL Server. So, it is always advisable to check whether the database name already exists or not. This can be done in two ways:

The following statement will only execute SQL 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
  • SQL Create database command 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

It 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 Databse 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
How to Create Database in SQL Server 5

How to Create Database in SQL Server Management Studio

In order to create new database in SQL server, first, open the SQL Server Management Studio. Right click on the Databases folder and select New database.. option from the context menu

How to Create Database in SQL Server 8

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 in Sql Server

How to Create Database in SQL Server 9

Let us see what will happen When we SQL create database with an existing name. As you can observe that it is throwing an error

How to Create Database in SQL Server 10

How to Delete Database in SQL Server

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

The syntax for SQL Delete database or Drop Database in SQL server is:

DROP DATABASE [Database Name]

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

DROP DATABASE [New_Database]

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

How to Create Database in SQL Server 6

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 SQL database name exists or not. Because sometimes, your colleges 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]

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 SQL drop database statement will be executed

DROP DATABASE [New_Database]
How to Create Database in SQL Server 7

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 database in Sql Server is:

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

In this example we rename New_Database with New_Db. So, within the query window, Please write the following query

SP_RENAMEDB  [New_Database],[New_Db]
How to Create Database in SQL Server 11

Placed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy