SQL Server DROP SCHEMA

Summary: in this tutorial, you will learn how to use the SQL Server DROP SCHEMA statement to remove a schema from a database.

SQL Server DROP SCHEMA statement overview

The DROP SCHEMA statement allows you to delete a schema from a database. The following shows the syntax of the DROP SCHEMA statement:

DROP SCHEMA [IF EXISTS] schema_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the schema that you want to drop. If the schema contains any objects, the statement will fail. Therefore, you must delete all objects in the schema before removing the schema.
• Second, use the IF EXISTS option to conditionally remove the schema only if the schema exists. Attempting to drop a nonexisting schema without the IF EXISTS option will result in an error.

SQL Server DROP SCHEMA statement example

First, create a new schema named logistics:

CREATE SCHEMA logistics;
GO
Code language: SQL (Structured Query Language) (sql)

Next, create a new table named deliveries inside the logistics schema:

CREATE TABLE logistics.deliveries
(
    order_id        INT
    PRIMARY KEY, 
    delivery_date   DATE NOT NULL, 
    delivery_status TINYINT NOT NULL
);
Code language: SQL (Structured Query Language) (sql)

Then, drop the schema logistics:

DROP SCHEMA logistics;
Code language: SQL (Structured Query Language) (sql)

SQL Server issued the following error because the schema is not empty.

Msg 3729, Level 16, State 1, Line 1
Cannot drop schema 'logistics' because it is being referenced by object 'deliveries'.
Code language: SQL (Structured Query Language) (sql)

After that, drop the table logistics.deliveries:

DROP TABLE logistics.deliveries;
Code language: SQL (Structured Query Language) (sql)

Finally, issue the DROP SCHEMA again to drop the logistics schema:

DROP SCHEMA IF EXISTS logistics;
Code language: SQL (Structured Query Language) (sql)

Now, you will find that the logistics schema has been deleted from the database.

In this tutorial, you have learned how to use the SQL Server DROP SCHEMA statement to remove a schema from a database.