DROP SCHEMA in SQL Server

The DROP SCHEMA statement could be used to delete a schema from a database. SQL Server have some built-in schema, for example : dbo, guest, sys, and INFORMATION_SCHEMA which cannot be deleted. Syntax :

DROP SCHEMA [IF EXISTS] schema_name;

Note : Delete all objects from the schema before dropping the schema. If the schema have any object in it, the output will be an error. Example : Let us create a new schema named geeksh (https://www.geeksforgeeks.org/create-schema-in-sql-server/);

CREATE SCHEMA geeksh;
GO

Now create a table named geektab inside the geeksh schema :

CREATE TABLE geeksh.geektab
(id INT PRIMARY KEY,
date DATE NOT NULL,
city varchar(200) NOT NULL);

Drop the schema geeksh : DROP SCHEMA geeksh; SQL Server will throw similar error as the schema is not empty.

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

Let us drop the table geeksh.geektab :

DROP TABLE geeksh.geektab;

Again run the DROP SCHEMA again to drop the geeksh schema :

DROP SCHEMA IF EXISTS geeksh;

Now, the geeksh schema has been deleted from the database.