SQL Server DROP SCHEMA

Learning Objective

In this tutorial, we will teach you how to use DROP SCHEMA in SQL Server to remove an existing schema from the SQL Server database.

SQL Server DROP SCHEMA Introduction

SQL Server DROP SCHEMA allows to drop an existing schema from the SQL Server database.

SQL Server DROP SCHEMA Syntax

The follwing shows the syntax of the SQL Server DROP SCHEMA statement.

DROP SCHEMA [IF EXISTS] schema_name;

In this syntax,

• DROP SCHEMA – keyword to drop an existing schema from the SQL Server database.
• IF EXISTS – Optional. This ensures that remove the schema only if the schema exists in SQL Server instance. Attempting to drop a non-existing schema SQL Server will give an error.
• schema_name – Name of the schema which you want to drop.

SQL Server DROP SCHEMA Example

To understand SQL Server DROP SCHEMA statement,

First, create a schema named college.

CREATE SCHEMA college;

Next, create a table named students inside the college schema.

CREATE TABLE college.students
(
   roll_no INT PRIMARY KEY IDENTITY,
   first_name nvarchar(50) NOT NULL,
   last_name nvarchar(50) NOT NULL,
   phone_no nvarchar(20),
   address nvarchar(255) NOT NULL
);

Now, drop the college schema.

DROP SCHEMA college;

SQL Server will give an error beause the schema is not empty. So, you can drop a schema only if the schema does not have any children in it.

Msg 3729, Level 16, State 1, Line 12
Cannot drop schema 'college' because it is being referenced by object 'PK__students__9560EEE1C13B2D13'.

Next, remove the students table from college schema.

DROP table college.students;

Finally, issue the DROP SCHEMA again to drop the schema.

DROP SCHEMA college;

Now, you will observe that college schema has been removed from the database.

Summary

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