What is Schema in SQL Server & How to Create or Drop Schema - SQL Server / T-SQL Tutorial Part 27

SQL Server / TSQL Tutorial :

What is Schema in SQL Server & How to Create or Drop Schema

Link to the script used in the video

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.

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.

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:
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]

Check out our website for Different SQL Server, MSBI tutorials and interview questions
such as SQL Server Reporting Services(SSRS) Tutorial
SQL Server Integration Services(SSIS) Tutorial
SQL Server DBA Tutorial
SQL Server / TSQL Tutorial ( Beginner to Advance)

[Blessy]

thx man hours of study and you explained in a few minutes

[RichardHema]

Very helpful indeed, thank you so much.

[square-deal]

Your video is very useful and informative, even almost a year later. Thanks

[malharjajoo]

If you make scheme owner a username, does this mean it does not belong to "public" role ?

[the_randomguy]

Hi sir whats schema integrity please reply having an interview on Tuesday

[tamilwargod]

thankSU well expaned. thankSU againNU

[malharjajoo]

Hi,

I am having trouble exporting few of my tables and the indexes, etc from one database to another on same database server ( on my local machine ) .
I have checked online for sql server scripts but I keep getting errors.

The error - " You do not have access to the object or it does not exist"

I have tried using the script against the correct database but it is still not working.
Could you help me out ?

[minaitconcepts]

If I watch more of these videos I learn to perfectly immitate an idian Accent.

[spicytuna]

i don't see how there can be a conflict with column names of a table. First of all, DB will not allow to create columns with same name.

[ashrr]

I find Micrfosoft's implementation of Schemas confusing and unnatural in comparison to Oracles.

[conaxlearn]

In most SQL Databases I see just dbo.<table name>. Is there any real world example where people need to use different schemas inside a database?

[MegaRafirafi]

Annoying & very confusing pronunciation!!