SQL Drop Database Command

Sometimes you’ll need to delete a database or drop a database in SQL. There’s a command to do this in SQL. Let’s see how you can do this in different databases.

Table of Contents

Dropping a Database

Deleting a database from your system is called “dropping”. The word delete usually refers to removing rows from a table, and “dropping” is deleting or removing an object, such as a table, or even a database.

So, dropping a database and deleting a database in SQL are the same thing, but that’s why they have two different names.

How can we drop a database?

Commands in Each Database

Here’s a summary of the commands to drop a database in each different database vendor.

Database	Command
Oracle	`shutdown immediate; startup mount exclusive restrict; DROP DATABASE;`
SQL Server	`DROP DATABASE [IF EXISTS] db_name;`
MySQL	`DROP DATABASE [IF EXISTS] db_name;`
PostgreSQL	`DROP DATABASE [IF EXISTS] db_name;`

In most cases, the command is:

DROP DATABASE [IF EXISTS] db_name;

Let’s look at each of them in more detail.

Oracle Drop Database

To drop a database in Oracle SQL, you run the Drop Database command. It works a little differently on Oracle compared to other databases as there is one database per connection, instead of multiple databases per connection in SQL Server or MySQL.

Here’s the command:

DROP DATABASE;

However, to do this, there are a couple of steps to take first.

Step 1: Shutdown the database.

shutdown immediate;

This will shutdown the database, closing all open connections and ensuring the database is in a valid state.

Step 2: Startup the database in “restrict” mode

startup mount exclusive restrict;

This will start up the database in exclusive restrict mode, meaning the database can be dropped.

Step 3: Drop the database.

Now you are connected, you can drop the database:

DROP DATABASE;

There’s no need to specify the name, as you’re already connected.

SQL Server Drop Database

To drop a database in SQL Server, use the Drop Database command:

DROP DATABASE [IF EXISTS] {database_name | database_snapshot_name};

You can run this when you are connected to your server.

The IF EXISTS keyword is optional and will drop the database if it exists. If the database does not exist, then no error will be shown. Without this keyword, you’ll get an error saying that the database does not exist.

You’ll need to provide the database name that needs to be dropped. You can also drop a database snapshot instead if needed. If you’re not sure what the database name is, you can look it up.

Here’s an example. To drop a database called my_db, your command would be:

DROP DATABASE my_db;

The database would then be dropped.

A database can be dropped regardless of its state. When you drop the database, it also deletes the physical database files from the server.

MySQL Drop Database

To drop a database in MySQL, you also use the Drop Database command:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

This command allows you to use either the Database or Schema keyword. Schema is a synonym for the word “database” in this command, so they both do the same thing.

You can also provide the IF EXISTS keyword. The IF EXISTS keyword is optional and will drop the database if it exists. If the database does not exist, then no error will be shown. Without this keyword, you’ll get an error saying that the database does not exist.

For example, to drop the database called my_db, the MySQL command would be:

DROP DATABASE my_db;

This command removes the database and also removes the directory and files on the database server for the database.

Just like the other databases here, there is no MySQL “delete database” command specifically, but the Drop Database command is used to drop or delete a database.

PostgreSQL Drop Database

The command to drop a database in PostgreSQL is Drop Database. The syntax looks like this:

DROP DATABASE [IF EXISTS] db_name;

The IF EXISTS keyword is used to prevent errors when attempting to drop a database that does not exist.

You can’t run this command unless you have the right privileges (superuser or database owner). You also can’t drop a database if there are active connections to the database. If there are active connections, you’ll need to connect to another database first, then drop this database.

Here’s an example of dropping a database called my_db:

DROP DATABASE my_db;

The database will then be dropped.

Conclusion

Dropping a database is fairly simple to do with the Drop Database command. It works in a similar way in each database and will delete the files used by the database.