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 );