DROP SCHEMA in SQL Server

In SQL Server, the DROP SCHEMA statement is used to remove a schema and all its objects from the database.

The syntax for the DROP SCHEMA statement is as follows:

DROP SCHEMA schema_name [CASCADE | RESTRICT];

• schema_name: The name of the schema to be dropped.
• CASCADE: Optional keyword that specifies that all objects in the schema should be dropped along with the schema. If this keyword is not specified and the schema contains objects, the DROP SCHEMA statement will fail.
• RESTRICT: Optional keyword that specifies that the DROP SCHEMA statement should fail if the schema contains any objects. This is the default behavior if neither CASCADE nor RESTRICT is specified.

Example 1: Drop a schema without objects

DROP SCHEMA sales;

This statement will drop the sales schema from the database, assuming that it does not contain any objects.

Example 2: Drop a schema with objects

DROP SCHEMA hr CASCADE;

This statement will drop the hr schema from the database and all objects contained within it, such as tables, views, and stored procedures.

Example 3: Restrict dropping a schema with objects

DROP SCHEMA production RESTRICT;

This statement will fail if the production schema contains any objects. If the schema is empty, it will be dropped successfully.