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. 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
); 
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.