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.
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.
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.