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 );