Create, Alter and Drop schema in MS SQL Server

In this article, we will be discussing about schema and how to create, alter and drop the schema.

1. Create schema :
A schema is usually a collection of objects. The objects can be tables, triggers, views, procedures etc. A database may have one or more schemas. SQL Server provides a feature of pre-defined schemas. The names of pre-defined schemas are much similar to that of built-in schemas.

A user can create schema using the syntax mentioned below.

Syntax –

create schema schema_name
[AUTHORIZATION owner_name] 

Authorization is a keyword which provides the authority over the schema. He/She has the control over the resources that can be accessed and security can also be provided. The name of the owner must be provided under owner_name. It can also be changed using alter schema which will be further discussed.

For better understanding, an example is mentioned below –

Example –

create schema student
GO 

Output –
The GO command executes the statement and a new schema is created.

2. Alter schema :
Alter is generally used to change the contents related to a table in SQL. In case of SQL Server, alter_schema is used to transfer the securables/contents from one schema to another within a same database.

Syntax –

alter schema target_schemaname
TRANSFER [entity_type::] securable name 

• target_schemaname is the name of the schema in which the object/contents should be transferred.
• TRANSFER is a keyword that transfers the contents from one schema to the other.
• entity _type is the contents or kind of objects that are to be transferred.
• securable_name is the name of the schema in which the object is present.

When a user moves the contents of the schema to another schema, SQL server will not change the name of the schema. In case, a user wants to change the name, drop_schema has to be used and the objects needs to be re-created for the new schema. When the object is moved, the SQL server will not update automatically, it must be manually modified by user.

Example –
A table named university has two schemas:

student and lecturer  

If suppose, the marks of the students has to be transferred to the lecturer schema, the query is as follows –

alter schema student
TRANSFER [marks::] lecturer 

This way, the marks are transferred to the lecturer schema.

3. Drop schema :
Ddrop_schema is used when the schema and its related objects has to be completely banished from the database including its definition.

Syntax –

drop schema [IF EXISTS] schema_name 

IF EXISTS is optional yet if a user wants to check whether a schema actually exists in database or not. Schema_name is the name of the schema in the database.

Example –

drop schema [IF EXISTS] student 

• Student is a schema that is actually present in the university database.
• The schema is dropped from the database along with its definition.