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.