In SQL, a schema is a logical partition in a database that contains a group of database objects, including, tables, views, stored procedures, indexes, triggers, and others. A schema belongs to a single database, whereas a database may have multiple schemas. Built-in schemas include dbo, guest, sys, and DEFAULT_SCHEMA.
Note: The word schema is also used to describe a data model, but that is NOT what this page is about. Here we describe a logical partition in a database, like dbo.
Schemas offer multiple benefits in managing a database.
Manage database objects as a group.
Apply different security permissions to database objects based on the schema.
Allow database objects with the same name in different schemas.
Access and manipulate database objects with less complexity.
Flexibility: objects may be freely moved among schemas.
Syntax to create a schema:
CREATE SCHEMA [schema_name] [AUTHORIZATION owner_name]
This creates a new schema in the current database.
Syntax to change a schema:
ALTER SCHEMA schema_name [TRANSFER object_name]
This moves database objects between schemas.
Syntax to remove a schema:
DROP SCHEMA [schema_name]
This removes a schema from the current database.
The schema cannot contain database objects, or else the removal will fail.
