In this article we will learn how to Creating, altering and dropping a database in SQL ?
Creating, altering and dropping a database in SQL
A SQL Server database we can be created, altered and dropped by two way
1. Graphically using SQL Server Management Studio (SSMS) or
2. Using a Query
To create the database graphically. Always the database name should be unique within the RDBMS.
1. Right Click on Databases folder in the Object explorer
2. Select New Database
3. In the New Database dialog box, enter the Database name and click OK. You can see in screenshot.
2. To Create the database using a query.
The Create database statement is used to create a new SQL database.
Create database tblEmp
Notes: tblEmp is a database name. Make sure you have admin privilege before creating any database. Once a database is created, you can check it in the list of databases with the following SQL command: SHOW DATABASE;
When, you create a database graphically using the designer or, using a query, these 2 files automatically generated.
.MDF file – Data File (Contains actual data)
.LDF file – Transaction Log file (Used to recover the database)
To alter a database, once it’s created
Alter database DatabaseName Modify Name = tblEmp1
Notes: tblEmp1 is a new database name.
Alternatively, you can also use system stored procedure
Execute sp_renameDB ‘tblEmp’,’tblEmp1′
To Delete or Drop a database
Drop Database tblEmp
Dropping a database, deletes the LDF and MDF files.
We cannot drop a database, if it is currently in use. You get an error stating – Cannot drop database “NewDatabaseName” because it is currently in use. So, if other users are connected, you need to put the database in single user mode and then drop the database.
Alter Database DatabaseName Set SINGLE_USER With Rollback Immediate
With Rollback Immediate option, will rollback all incomplete transactions and closes the connection to the database.
Notes: System databases cannot be dropped.
