Skip to content

SQL DROP DATABASE

SQL DROP DATABASE statement is a powerful command used to permanently delete an existing database from a database management system (DBMS).

In this article, We will learn about SQL DROP DATABASE in detail by understanding various examples in detail.

SQL DROP DATABASE

  • SQL DROP DATABASE statement is used to delete an existing database from the database management system (DBMS).

  • This operation permanently removes the database and all its associated data, including tables, views, stored procedures, and any other objects contained within the database.

Syntax

SQL DROP TABLE syntax is:

DROP DATABASE database_name;

Examples of SQL DROP DATABASE

Let’s look at some examples of the DROP DATABASE statement in SQL.

First, let’s create a database on which we will run the query.

CREATE DATABASE GeeksForGeeks;

Now if we look at the list of databases in our system, we can find the “GeeksforGeeks” database.

Query:

SHOW DATABASES;

Output:

Show Database

Now we will use the DROP DATABASE command, to delete the database ‘GeeksforGeeks’.

Query:

DROP DATABASE GeeksForGeeks;

Output:

Drop Database

Verify DROP DATABASE Statement

After the database has been deleted/dropped successfully we will now verify that whether the database exist in the system or not. So, we will once again use the SHOW DATABASES command and verify that the GeeksForGeeks database has been deleted or not.

Query:

SHOW DATABASES;

Output:

Verify Drop Database

SQL DROP DATABASE IF EXISTS

To avoid any error while running the DROP DATABASE command use the IF EXISTS clause, which will delete the database only if it exists in the system.

Query:

DROP DATABASE IF EXISTS Database_Name;

Important Points About SQL DROP DATABASE Statement

  • Deleting a Database: Deleting a database is a permanent action and will result in the loss of all information stored in the database.

  • Backup: Its always been a good practice to take proper backup of the database before performing any delete operation on it.

  • Privileges: Make sure you have the necessary privilege to delete the database. Usually an admin can delete the database.

  • Database State: A database can be dropped regardless of its state: offline, read-only, suspect, and so on.

  • Database Snapshots: Dropping a database snapshot deletes the database snapshot from an instance of SQL Server and deletes the physical NTFS File System sparse files used by the snapshot.

  • Replication: To drop a database published for transactional replication, or published or subscribed to merge replication, you must first remove replication from the database.

  • System Databases: System databases cannot be directly dropped or edited by the user.

Conclusion

The DROP DATABASE statement is essential for managing databases within a DBMS, allowing administrators to completely remove databases that are no longer needed. While it is a straightforward command, it is crucial to ensure that backups are taken and that the appropriate privileges are in place before executing it.