Schemas in SQL Server

Schemas in SQL Server are used to logically group database objects, such as tables, views, and stored procedures. Here’s how we can create and delete schemas.

Creating a Schema

To create a schema, you use the CREATE SCHEMA statement. Here’s the basic syntax and an example:

SQL Syntax CREATE SCHEMA schema_name;

Example:

SQL

CREATE SCHEMA Sales;
CREATE SCHEMA Inventory;
CREATE SCHEMA HR;

Deleting a Schema

To delete a schema, you use the DROP SCHEMA statement. Note that a schema must be empty before it can be deleted; you must first drop or move any objects that exist within the schema.

SQL Syntax DROP SCHEMA schema_name;

Example: Assume you have the Sales schema with some tables in it. First, you need to drop those tables or move them to another schema before you can drop the Sales schema.
Step 1: Drop or Move Objects in the Schema

SQL

-- Dropping tables (or other objects) within the schema
DROP TABLE Sales.Orders;
-- Alternatively, you could move the table to another schema
ALTER SCHEMA dbo TRANSFER Sales.Orders;

Step 2: Drop the Schema

SQL

DROP SCHEMA Sales;

Previous Next