Skip to content
Related Articles

Related Articles

Improve Article
CREATE SCHEMA in SQL Server
  • Last Updated : 02 Sep, 2020

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 –

nameschema_idprincipal_id
dbo11
guest22
INFORMATION_SCHEMA34
sys44
db_owner1638416384
db_accessadmin1638516385
db_securityadmin1638616386
db_ddladmin1638716387
db_backupoperator1638916389
db_datareader1639016390
db_datawriter1639116391
db_denydatareader1639216392
db_denydatawriter1639316393

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
); 
My Personal Notes arrow_drop_up
Recommended Articles
Page :