In this article, we will discuss how to Create, Alter, and Drop a database in SQL Server.
In my previous article, we have discussed all about the Types of SQL Commands.
Database : In the SQL server a database is a place to store the data in an electronic format. It is an organized collection of database objects such as tables, views, functions, stored procedures, triggers and so on.
It allows a user to easily access or manipulate the data.
Create Database In SQL Server
In SQL Server, there are two ways to create a user-defined database, either by using the Transact-SQL statements or by using SQL Server Management Studio.
CREATE DATABASE Statement In SQL Server
In SQL Server the CREATE DATABASE statement is used to create a new user-defined database.
Following is the syntax for creating a SQL Server database.
CREATE DATABASE DatabaseName;
Example to create a new database “SampleDB” in the SQL server.
CREATE DATABASE SampleDB;
Once we run the above SQL statement, it will create a new database called “SampleDB” in the SQL server.
Create Database using SQL Server Management Studio(SSMS)
Step 1: Right Click on the Database folder from the Object Explorer window and then select the ‘New Database’ option shown in the below image.
Step 2: A new database dialog box will appear, enter the new Database name and click on the OK button as shown in the below image.
Step 3: Once we clicked on the OK button on the above dialog box, it will create a new database “SampleDB” as shown in the below image.
How to Alter a database in SQL Server?
After the creation of a new database, we can rename the database either by Transact-SQL command or by using SQL Server Management Studio.
Alter Database Using T-SQL Command
Now Let’s Rename the Database using the T-SQL ALTER DATABASE command or by using the system-defined stored procedure SP_RENAMEDB .
Following is the syntax.
ALTER DATABASE OldDatabaseName MODIFY NAME = NewDatabaseName ; -- OR EXECUTE SP_RENAMEDB 'OldDatabaseName','NewDatabaseName';
Following is the example to rename a database in SQL Server.
-- Using ALTER command ALTER DATABASE SampleDB MODIFY NAME = MyDatabase; -- OR -- Using system-defined stored procedure EXECUTE SP_RENAMEDB 'SampleDB','MyDatabase';
After executing the above SQL commands, refresh the “Databases” folder in the “Object Explorer” window to see the new database name as shown in the below image.
Rename Database Using SQL Server Management Studio
Now Let’s rename the database from “MyDatabase” to “SampleDB” using the SQL server management studio.
To rename the database select the database from the “Databases” folder > Right-click and select the Rename option as shown in the below image.
Note: Before rename, Always make sure that the database is not in use otherwise it will not allow you to change the name of the database.
How to Delete a database in SQL Server?
In SQL Server, we can delete or drop a database in two ways, either by using the T-SQL DROP DATABASE statement or by SQL Server Management Studio (SSMS).
DROP Database Using T-SQL Command
Following is the syntax to drop a database.
DROP DATABASE DatabaseName ;
Following is the example to delete the “SampleDB” database.
DROP DATABASE SampleDB ;
DROP Database Using SQL Server Management Studio
To delete a database, select the database from the “Databases” folder > Right-click and select the Delete option as shown in the below image.
When we delete a database either by T-SQL command or by SSMS, two files called LDF and MDF are deleted internally.
We can CREATE, ALTER, and DROP a database either by Transact-SQL command or by using SQL Server Management Studio (SSMS).
We can only delete user database, not the system-defined database such as master , model , msdb , and tempdb .
Hope you enjoyed this post. Thanks for visiting.
I am a Sr. Software engineer. I started this blog as a way to contribute back to the developer community for everything I have learned along the way from others.