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.
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 –
create schema student GO
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.
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.
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.
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.
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.
- ALTER SCHEMA in SQL Server
- DROP SCHEMA in SQL Server
- SQL | ALTER (ADD, DROP, MODIFY)
- CREATE SCHEMA in SQL Server
- Difference between Star Schema and Snowflake Schema
- Difference between Snowflake Schema and Fact Constellation Schema
- Difference between Star Schema and Fact Constellation Schema
- Alter login in SQL Server
- Difference between ALTER and UPDATE Command in SQL
- SQL | ALTER (RENAME)
- Drop login in SQL Server
- CREATE and DROP INDEX Statement in SQL
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- Create login in SQL Server
- Create Database in MS SQL Server
- Difference between DELETE and DROP in SQL
- Difference between DROP and TRUNCATE in SQL
- SQL | DROP, TRUNCATE
- Difference between Schema and Database
- Difference between Schema and Instance in DBMS
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.