A database in SQL Server is a storage location where we can store our business data. The 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, and Delete DB with an example of each.
How to Create Database in SQL Server example
Before we start creating a new database in SQL Server, Let us see the list of available ones. The list of available dbs in the current instance is.
The syntax for SQL create Database is:
CREATE DATABASE DatabaseName
In this example, we create a new database called New_Database. So, Replace the DatabaseName with New_Database in the Server query window.
CREATE DATABASE New_Database;
Click on the Execute button to execute the command.
The Create Database 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
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 name.
How to Check whether SQL Database name exists or not
In an organization, we may or may not have the privilege to know the available databases. So, it is always advisable to check whether the 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.
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 db already exists
- SQL Create database command only executes, if the New_database is not available in a system db
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 db name New_database in the system.
SELECT name FROM master.dbo.sysdatabases
WHERE name = N'New_Database'
If the db 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 db already exists
SELECT 'Database Name already Exist' AS Message
How to Create Database in SQL Server Management Studio
In order to create new database in SQL server, first, open the Management Studio. Right click on the below. Shown folder and select New .. option from the context menu
Once you select the option, following window will be opened. Here we left Owner as default and db name as New_database as shown below. Click OK to create new database in Sql Server
Let us see what will happen When we create db with an existing name. As you can observe that it is throwing an error
How to Delete Database in SQL Server
To Delete a database in SQL server, we can simply use the following syntax and the syntax for Drop is:
DROP DATABASE [DatabaseName]
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 Drop command once again:
From the above screenshot, you can observe that it is throwing an error saying: New_database doesn’t exist.
A better approach is to check whether the name exists or not. Because sometimes, your colleagues or your team leader may delete the DB which you are trying to delete.
IF EXISTS
(
SELECT name FROM master.dbo.sysdatabases
WHERE name = N'New_Database'
)
DROP DATABASE [New_Database]
Messages
-------
Command(s) completed successfully.The following statement will check for the db name New_database in the system.
SELECT name FROM master.dbo.sysdatabases
WHERE name = N'New_Database'
If the db exists, then only following drop statement will be executed
DROP DATABASE [New_Database]
How to Rename Database?
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 Name],[New 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]
Messages
-------
The database name 'New_Db' has been set.