CREATE SCHEMA in SQL Server

A schema is a collection of database objects like tables, triggers, stored procedures, etc. A schema is connected with a user which is known as the schema owner. The database may have one or more schema.

To create a schema in SQL Server use the 'CREATE SCHEMA' Statement.

SQL CREATE SCHEMA

CREATE SCHEMA statement used to create a new schema in the current database.

The CREATE SCHEMA statement can also create tables and views within the new schema and set GRANT, DENY, or permissions on those objects

It is useful to enhance the structure, security, and manageability of SQL Server databases.

Syntax

CREATE SCHEMA syntax is:

CREATE SCHEMA schemaname
[AUTHORIZATION ownername]GO

In this Syntax,

• Mention the name of the schema that you want to create in the CREATE SCHEMA clause.
• Define the owner of the schema after the AUTHORISATION keyword.

Note:

SQL Server has some built-in schema, for example, dbo, guest, sys, and INFORMATION_SCHEMA.

dbo is the default schema for a new database, owned by dbo user. While creating a new user with CREATE USER command, the user will take dbo as its default schema.

SQL CREATE SCHEMA Example

Let's look at some examples on how to create schema in SQL server using CREATE SCHEMA command.

Creating Schema in SQL Server Example

In this example, we are creating a schema named geeks_sch.

Query:

CREATE SCHEMA geeks_sch;
GO 

View All SQL Server SCHEMA

To list all schema in the current database, use the query as shown below :

SELECT  *
FROM sys.schemas 

Result

Create Objects for the Schema

To create objects for a schema in SQL Server, you can use the CREATE TABLE statement with the schema name specified.

Syntax

CREATE TABLE schemaname.tablename( values... );

Create Objects for the Schema Example

To create a new table named Geektab in the geeks_sch schema:

CREATE TABLE geeks_sch.Geektab(
G_id INT PRIMARY KEY IDENTITY, 
Name VARCHAR(200), 
DOJ DATETIME2 NOT NULL
);