SQL DROP DATABASE

SQL DROP DATABASE statement deletes an existing database from the system, and once deleted, users can not retrieve that database.

The DROP DATABASE command in SQL deletes the database along with its data, like tables, views, indexes, constraints, and properties like permissions and schemas.

SQL DROP TABLE syntax is:

DROP DATABASE database_name;

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 w3wiki;

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

Query:

SHOW DATABASES;

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

Query

DROP DATABASE w3wiki;

Output

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 w3wiki database has been deleted or not.

Query

SHOW DATABASES;

Output

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.

Syntax

DROP DATABASE IF EXISTS Database_Name;

• Deleting a Database: Deleting a database is a permanent action and will result in the loss of all information stored in the database.
• Backup: Make sure you have taken proper backup of the database before you delete 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 dropped