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
);
Last Updated :
02 Sep, 2020
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...