SQL Server Create SCHEMA

A Schema in SQL Server is a collection of database objects associated with a database. It always belongs to a database. The default schema for any database is dbo.

It includes various database objects including your tables, views, procedures, index, etc.

SQL Server also provides some pre-defined schemas which have the same names as the built-in database users and roles such as dbo, sys, guest, and INFORMATION_SCHEMA.

Schema always belongs to one database, and a database may have one or multiple schemas, therefore two tables in two schemas can share the same name.

For example: table dbo.employee and staging.employee can be created in same database. Because, they have different different schema name dbo and staging.

SYNTAX

CREATE SCHEMA schema_name
[AUTHORIZATION owner_name]

Lets look at an example of using SCHEMA in SQL Server.

Creating a new SCHEMA in database

Following create schema statement creates a new schema named as Staging in database Prod_Db.

USE Prod_Db
GO

CREATE SCHEMA Staging ; 

GO

Lets check whether the schema is created or not, it can be checked either by T-SQL code or using object explorer .

Using Object Explorer:

Go to Database > expand Security Folder > Next, expand Schemas Folder there you will see a schema newly created schema Staging.

Using SQL Query:

Following Statement returns all the list of available schema in database Prod_Db.

USE Prod_Db

SELECT * FROM sys.schemas

You can see, Staging schema is also there in result.

Creating a new table within Schema

As you have created a new schema Staging in database Prod_Db, Lets create a table in Staging schema.

Following statement creates a new table named as Employee in the Staging schema.

CREATE TABLE Staging.Employee
(
EmpId INT NOT NULL IDENTITY(1,1),
EmpName VARCHAR(100) NOT NULL,
EmpCode VARCHAR(10) NOT NULL
)

As you can see, Commands completed successfuly. Lets check whether a table Employee is created within Staging schema or not, you can check this using following statement.

SELECT t.name AS TableName , 
s.name AS SchemaName
FROM sys.tables t 
  INNER JOIN sys.schemas s 
  ON t.schema_id = s.schema_id 
WHERE t.name ='Employee'

As you can see above result, one employee table was already there in database within default schema dbo and now you have just created one more table with same name within another schema that is Staging.

It clearifies that two table with same name can be created in same database if they are created in different different schema

You can also check same in object explorer inside table folder there you can see two employees tables. Both table have different different schema.

Transfer a table from one SCHEMA to another SCHEMA

You can also move an existing table from one schema to another schema.

As you can see below screenshots table DumpData is within default schema that is dbo, and you want to transfer it under Staging schema.

 

Lets verify same using T-SQL Statement as shown below.

SELECT t.name AS TableName , 
s.name AS SchemaName
FROM sys.tables t 
  INNER JOIN sys.schemas s 
  ON t.schema_id = s.schema_id 
WHERE t.name ='DumpData'

 

Lets transfer the table DumpData from Schema dbo to Staging.

Following statement transfer DumpData table’s schema from dbo to staging.

ALTER SCHEMA Staging TRANSFER DumpData ;

Lets make sure whether table DumpData is transferred to Staging schema or not, you can use same statement again to check for table schema.

Same can be checked in object explorer.

Dropping a SCHEMA

You can also drop an existing schema, but before dropping schema you will have to check whether any object that is being referenced by this schema or not.

As you have just transferred the table DumpData to Staging schema, and Employee table is also created under Staging schema, so you can not simply delete Staging schema as these two tables are being referenced by the schema so first you will have to transfer these two table to another schema, or drop the table if no need of them.

You can see table Employee and DumpData are within Staging Schema.

Lets try to drop the staging schema directly first to see what happens.

DROP SCHEMA Staging

As you see above screenshot, it gives an error saying, schema is being referenced by Object DumpData. It means schema can not be dropped untill all the objects those are using schemas is removed first or moved to another schema.

Lets Drop the table DumpData from Database, here we are dropping the table as it was just for demo purpose, you can move it to another schema or drop if no need.

DROP TABLE Staging.DumpData

 

Lets try to Drop Staging Schema again by DROP Shcema Statement as shown below.

DROP SCHEMA Staging

And you will see, again it gives an error this time it says Staging Schema is being referenced by Employee table.

This is somthing that we already knew that table DumpData and Employee both referencing to Staging Schema.

As in previous step we just dropped DumpData table only although we knew that Employee table also referencing the Staging schema, so this time SQL Server gave an error for Employee table in this step.

So we should always take out the name of all the objects first which are being referenced by schema and remove, or transferred them to another schema at once for reducing effort.

 

Lets Drop Employee table from database, again we are dropping this table as it was just for demo.

DROP TABLE Staging.Employee

 

Lets verify if still any objects using Staging schema or not,  and you can see the output of following statement there is no objects that is being reference by staging schema now.

SELECT t.name AS TableName,
s.name AS SchemaName
FROM sys.tables t
   INNER JOIN sys.schemas s
   ON t.schema_id = s.schema_id

As you can see , there is no objects that is being referenced by staging schema now, now you can drop the Staging schema using drop schema statement.

DROP SCHEMA Staging

 

As you can see , this time it is dropped without any error. You can also verify the same either using object explorer or executing T- SQL Statement.

Using Object Explorer:

Under Database, Expand Secuirty folder then expand Schema folder. There you will see Staging Schema does not exists.

Using T-SQL Code:

USE Prod_Db

SELECT * FROM sys.schemas

Also Read..

Create Database in SQL Server

Create Table in SQL Server