SQL Schema

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.

SQL Schema Benefits


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.

Create SQL Schema

Syntax to create a schema:

CREATE SCHEMA [schema_name] 
  [AUTHORIZATION owner_name]

This creates a new schema in the current database.

Alter SQL Schema

Syntax to change a schema:

ALTER SCHEMA schema_name 
  [TRANSFER object_name]

This moves database objects between schemas.

Drop SQL Schema

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.

You may also like


vsn 3.1