When we work with the database there are very rare cases when we want to drop the complete database. However, sometimes it’s required to do a complete cleanup and fresh database setup.
The meaning of dropping a database is that all the tables in the database will be deleted along with the data that is stored in the table.
Now we will learn how to drop the database for the below-mentioned databases.
- MySQL Drop Database
- PostgreSQL Drop Database
- SQL Server Drop Database
We will go in detail for all the above mentioned DBs.
MySQL Drop Database
We will now try to understand how to drop a database in MySQL.
Please note that the below-mentioned script should be run on MySQL shell prompt.
Drop Database Syntax: –
|
1 2 |
DROP DATABASE [IF EXISTS] database_name; |
It is important to add the IF EXISTS clause as MYSQL will give an error if we try to drop a database that does not exist.
Example for Drop Database: –
|
1 2 |
DROP DATABASE IF EXISTS TestDB; |
The above query will drop the database with the name as TestDB.
PostgreSQL Drop Database
Assume that the database that we want to drop is TestDB.
The drop database command can only be executed by the user having owner permission on the TestDB.
Also, it is always better to test the connection to the database. Before executing the drop command it is good to make sure that the database is not connected.
Connecting to another database will ensure that the connection is disconnected from the target database.
Let us now try to drop TestDB.
- Right-Click on the database and click on Delete/Drop option.
- Drop database confirmation screen will appear.
- Click on OK and the database will get dropped.
PostgreSQL Right Click Drop
TestDB is successfully dropped.
Please find below the script that can be used as an alternative to the UI.
|
1 |
dropdb -p 5000 -h admin -i -e demo |
To destroy the database demo using the server on host admin, port 5000.
It is useful to use the dropdb program from PostgreSQL which is a wrapper around drop database command.
SQL Server Drop Database
We will not try to use DROP command to drop a database in SQL server.
The following steps need to be followed to drop a database in SQL Server.
- Launch SQLServer Management Studio and authenticate to log in.
- In the Object Explorer, select the database that you want to drop. We will be selecting TestDB for our purpose. Right click and click on delete.
- A pop-up screen will appear. Click on OK and the database will get dropped.
SQLServer Right Click Drop
SQLServer Drop Screen
TestDB is dropped successfully.