Create, Alter & Drop schema

Create a schema

Schema is basically the logical representation of the database. there is a default schema named dbo. Objects gets created inside a schema when ‘create schema’ statement is used. To provide access to other user after the schema is created,we need to impersoinate permissions.

Syntax:The syntax to create a schema is −

Create schema schema_name

Here,we have created a schema named schema_name.

Example 1: Granting permissions

In this example, we are going to create a schema and grant permissions to have access to that.

Algorithm

• Step 1 − Create a schema.

• Step 2 − Create a table.

• Step 3 − Assign whether to grant or deny permissions.

• Step 4 − Execute as granted user

• Step 5 − Use select to get the output

Input

Employee

Emp_id	Name	Dept_name	Salary
1	Monu	IT	50000
2	Sonu	HR	60000
3	Golu	Security	70000

Code

Create Schema account#schema is created
   authorization amrendra#authorization is given
grant select on schema ::account to x#granting access to x
Execute as user=’x’; #x is using the schema now
Select * from employee; #data selected
deny select on schema::account to y;denying access to y. 

Output

Emp_id	Name	Dept_name	Salary
1	Monu	IT	50000
2	Sonu	HR	60000
3	Golu	Security	70000

Example 2: Creating a schema and a table in the schema.

In this example, we are going to create a schema and then create a table in that schema.

Algorithm

• Step 1 − Create a schema

• Step 2 − Create table using schema_name.table_name inside schema.

• Step 3 − Select statement is used to extract the table which is inside the schema

Input

Employee

Employee_id	Employee_name	department_name	salary
1	Rahul	developer	40000
2	Monu	hr	50000
3	Aman	consulting	60000
4	Naman	manager	70000

Code

Create Schema account;#schema is created
Create table account.employee(employee_id int,employee_name char(50),department_name char(50),salary int);#table is created
Select * from account.employee;#extracting the table data

Output

Employee

Employee_id	Employee_name	department_name	salary
1	Rahul	developer	40000
2	Monu	hr	50000
3	Aman	consulting	60000
4	Naman	manager	70000

Example 3:Owner of the schema

In this example, we are going to create a schema and then set its owner.

Algorithm

• Step 1 − Create Schema

• Step 2 − Provide authorization by setting the owner

• Step 3 − Execute the schema as the owner

• Step 4 − Use select to get the output

Input

Employee

Emp_id	Name	Dept_name	Salary
1	Monu	IT	50000
2	Sonu	HR	60000
3	Golu	Security	70000

Code

Create schema account  #schema named account is created
authorization [amrendra] #The schema is owned by amrendra. 
Execute as user = ‘amrendra’ #amrendra is using the schema now
Select * from account;#data selected

Output

Emp_id	Name	Dept_name	Salary
1	Monu	IT	50000
2	Sonu	HR	60000
3	Golu	Security	70000

Alter a schema

Here, we are going to change the schema which leads to transferring the content from one schema to another within the same database.

Syntax

alter schema schema_name
Transfer[entity_type::] securable_name 

• schema_name is the target schema in which the content should be moved.

• securable_name is the pre-existing schema name.

Example

In this example,we are going to transfer the ownership of the table by transferring the table address from one schema to another.

Algorithm

• Step1 − Create a table in which the default schema is used.

• Step2 − Create a schema in which you want to transfer.

• Step 3 − Use transfer the table content onto the new schema.

Input

Employee

Emp_id	Name	Dept_name	Salary
1	Monu	IT	50000
2	Sonu	HR	60000
3	Golu	Security	70000

Code

Create table dbo.employee(employee_id int,employee_name char(50),department_name char(50),salary int);
#table is created in default schema
Create schema patel;#new schema created
ALTER SCHEMA patel TRANSFER object::dbo.employee;
# table objects are transferred in the new schema.      

Output

Patel

Emp_id	Name	Dept_name	Salary
1	Monu	IT	50000
2	Sonu	HR	60000
3	Golu	Security	70000

Drop a schema

Here, we are going to see how to drop a schema. drop schema is used when we need to banish the schema completely.

Syntax

Drop schema [if exists] schema_name
schema_name is the name of the schema which will be dropped. 
Note that this query will successfully execute only if the schema already exists. 

Example

In this example, we are going to see how the existing schema is banished along with the table that is contained by that schema.

Algorithm

• Step 1 − Create a schema providing its owner

• Step 2 − Create table in the schema

• Step 3 − Drop table which is in the schema

• Step 4 − Drop the Schema.

• Step 5 − Use select statement to crosscheck

Input

Emp_id	Name	Dept_name	Salary
1	Monu	IT	50000
2	Sonu	HR	60000
3	Golu	Security	70000

Code

Create Schema account authorization amrendra#schema created with its owner. 
create table employee(employee_id int,employee_name char(50),department_name char(50),salary int);
#table is created in schema drop table account.employee; 
#table is droppeddrop schema account; 
#schema is dropped
Select * from account;#data selected

Output

No data found

Conclusion

There are three operations performed related to sql. The first operation is to create a schema which is explained with three examples, the first one is to grant permission of the schema, the second one is to create a table contained by the schema and in third one, specific member is provided the authorization of the schema created. second operation is to alter a schema in which contents from one schema to the other schema. The third operation is to drop the schema in which we have banished the schema along with its table.