A schema is a collection of database objects (tables, views, functions, indexes ,etc) associated with one particular database username. This username is called the schema owner. A database can have one or multiple schemas.

An object within a schema is qualified using the schema_name.object_name format. Say a user’s database username is USER1. If this user log on to the database and create a table called EMPLOYEE_TBL. Table’s actual name in the database is USER1.EMPLOYEE_TBL. The schema name for that table is USER1. There are some pre-defined schemas which have the same names as the built-in database users and roles. The default schema for a newly created database is dbo.

Create Schema

To create a new schema in the current database, use CREATE SCHEMA statement. Syntax is

CREATE SCHEMA schema_name
[AUTHORIZATION owner_name]

In this syntax, schema_name is the name of the schema. Also specify the owner of the schema after the AUTHORIZATION keyword.

Example

Following example create the customer_services schema using CREATE SCHEMA statement.

CREATE SCHEMA customer_services;

To list all schemas in the current database, query schemas from the sys.schemas as shown below

SELECT 
    s.name AS schema_name, 
    u.name AS schema_owner
FROM 
    sys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY 
    s.name;

Following statement creates a new table named jobs in the customer_services schema:

CREATE TABLE customer_services.jobs(
    job_id INT PRIMARY KEY IDENTITY,
    description VARCHAR(200),
    created_at DATETIME2 NOT NULL
);