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. The database may have one or more schema.
To create a schema in SQL Server use the 'CREATE SCHEMA' Statement.
SQL CREATE SCHEMA
CREATE SCHEMA statement used to create a new schema in the current database.
The CREATE SCHEMA statement can also create tables and views within the new schema and set GRANT, DENY, or permissions on those objects
It is useful to enhance the structure, security, and manageability of SQL Server databases.
Syntax
CREATE SCHEMA syntax is:
CREATE SCHEMA schemaname
[AUTHORIZATION ownername]GO
In this Syntax,
- Mention the name of the schema that you want to create in the CREATE SCHEMA clause.
- Define the owner of the schema after the AUTHORISATION keyword.
Note:
SQL Server has some built-in schema, for example, dbo, guest, sys, and INFORMATION_SCHEMA.
dbo is the default schema for a new database, owned by dbo user. While creating a new user with CREATE USER command, the user will take dbo as its default schema.
SQL CREATE SCHEMA Example
Let's look at some examples on how to create schema in SQL server using CREATE SCHEMA command.
Creating Schema in SQL Server Example
In this example, we are creating a schema named geeks_sch.
Query:
CREATE SCHEMA geeks_sch;
GO
View All SQL Server SCHEMA
To list all schema in the current database, use the 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 objects for a schema in SQL Server, you can use the CREATE TABLE statement with the schema name specified.
Syntax
CREATE TABLE schemaname.tablename( values... );
Create Objects for the Schema Example
To create a new table named Geektab in the geeks_sch schema:
CREATE TABLE geeks_sch.Geektab(
G_id INT PRIMARY KEY IDENTITY,
Name VARCHAR(200),
DOJ DATETIME2 NOT NULL
);
