Create, Alter and Drop schema in MS SQL Server

Schema management in MS SQL Server involves creating, altering, and dropping database schema elements such as tables, views, stored procedures, and indexes. It ensures that the database structure is optimized for data storage and retrieval.

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

What is 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 very similar to those of built-in schemas. A user can create a schema using the syntax mentioned below.

CREATE SCHEMA [schema_name] [AUTHORIZATION owner_name]
[DEFAULT CHARACTER SET char_set_name]
[PATH schema_name[, ...]]
[ ANSI CREATE statements [...] ]
[ ANSI GRANT statements [...] ];

Create Schema in MS SQL Server

To create a schema in MS SQL Server, use the CREATE SCHEMA statement. This statement allows you to create a new schema in the current database and define its elements, such as tables, views, and permissions.

Syntax

CREATE schema schema_name
[AUTHORIZATION owner_name];

• schema_name: The name of the new schema.
• AUTHORIZATION owner_name: Assigns ownership of the schema to the specified user. The owner has control over the resources and can provide security for the schema.

For better understanding, an example is mentioned below -

Example: Create a schema in MS SQL Server

Query:

CREATE SCHEMA student
GO 

Output:

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

Alter Schema in MS SQL Server

To alter a schema in MS SQL Server, use the ALTER SCHEMA statement with the TRANSFER clause.

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: Alter schema in MS SQL Server

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.

Query:

ALTER SCHEMA lecturer
TRANSFER student.marks;

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

Drop Schema in MS SQL Server

To drop a schema in MS SQL Server, use the DROP SCHEMA statement.

It completely deletes the schema and its related objects including its definition. Drop 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 (optional): If a user wants to check whether a schema actually exists in database or not.
• schema_name: The name of the schema in the database.

Example: Drop schema in MS SQL Server

To drop the student schema from the database, use the following query.

Query:

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.

This removes the student schema from the database along with its definition.

Conclusion

Schemas in MS SQL Server are crucial for organizing and managing database objects within a logical framework. By creating schemas, you can segregate different segments of your database for better clarity and security. Altering schemas allows you to modify their ownership, ensuring the right permissions and controls.

Dropping schemas removes them from the database, but this should be done with caution to avoid accidental loss of important objects. Proper use of schemas enhances database management, improves security, and provides a structured approach to handling database objects