What is Schema in SQL Server and How to Create/Drop Schema in SQL Server Database - SQL Server / TSQL Tutorial Part 27

What is Schema : 

As per Microsoft website :
A Schema is distinct namespace to facilitate the separation, management, and ownership of database objects.

How to create Schema by GUI:
Open the SSMS and you will be going to Database as Schema are objects in the database. Go to Security and then Go to Schema and Right Click and Choose New Schema as shown below.

How to create Schema in SQL Server Database - SQL Server Tutorial

Now you need to provide the Schema name. I used TB as Schema Name and I left the Schema Owner empty, that means that dbo will be owner for this schema. If you would like to choose owner, you can hit search and provide the owner of schema.

How to create Schema in SQL Server Database - SQL Server / TSQL Tutorial 

Create Schema by using TSQL :

To create Schema in Database with default owner that is dbo, you can use below script.

--DatabaseName=TechBrothersIT
USE [TechBrothersIT]
GO
--Create Schema Name =TB
CREATE SCHEMA [TB]
GO

Let's say If I have a user in TechbrothersIT Database with name [aamir] and I would like to create Schema TB with owner name =[aamir]. I can use below script.

--Database Name = TechBrothersIT
USE [TechBrothersIT]
GO
--Create Schema TB with owner name =Aamir ( Database User)
CREATE SCHEMA [TB] AUTHORIZATION [aamir]
GO

How to Drop Schema from SQL Server Database:

1) By using GUI
we can right click on schema name in a database and hit Delete as shown below.

How to Drop Schema from a SQL Server Database by using GUI- SQL Server Tutorial

We can also use TSQL Script to drop the Schema from a SQL Server Database. In below example, I am deleting TB Schema from TechBrothersIT Database.

--Database Name = TechBrothersIT
USE [TechBrothersIT]
go
--Drop Schema TB
Drop Schema [TB]

Video Demo: What is Schema and how to Create/ Drop Schema