Skip to content
Related Articles

Related Articles

Create, Alter and Drop schema in MS SQL Server
  • Last Updated : 12 Jun, 2020

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

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.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :