Open In App

CREATE SCHEMA in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

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. Database may have one or more schema.

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

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

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

Syntax :

CREATE SCHEMA schemaname
   [AUTHORIZATION ownername]
GO

Example –

CREATE SCHEMA geeks_sch;
GO 

To select SQL Server SCHEMA :
To list all schema in the current database, use query as shown below :

SELECT  *
FROM sys.schemas 

Result –

name schema_id principal_id
dbo 1 1
guest 2 2
INFORMATION_SCHEMA 3 4
sys 4 4
db_owner 16384 16384
db_accessadmin 16385 16385
db_securityadmin 16386 16386
db_ddladmin 16387 16387
db_backupoperator 16389 16389
db_datareader 16390 16390
db_datawriter 16391 16391
db_denydatareader 16392 16392
db_denydatawriter 16393 16393

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

Syntax :

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

Example –

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

Last Updated : 02 Sep, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads