Database Schema in SQL Server
In SQL Server, a schema is a logical collection of database objects such as tables, views, stored procedures, indexes, triggers, functions. It can be thought of as a container, created by a database user. The database user who creates a schema is the schema owner.
- A schema can belong to only one database whereas a database can have one or multiple schemas.
- There are no restrictions on the number of objects in a schema.
- SQL Server provides us with a few built-in schemas such as dbo, guest, sys, etc.
- A database schema can be owned by a database role or an application role along with the database user. They are called schema owners.
- dbo is the default schema for a newly created database.
- Schema ownership can be transferred from one user to another user in the same database.
- A database user can be dropped without dropping the database objects owned by the user. But the schema cannot be deleted if it owns database objects.
Create Schema Using T-SQL
A schema can be created using T-SQL.
CREATE SCHEMA <schema_name> [AUTHORIZATION owner_name]
In the above syntax,
schema_name is the name of the schema you want to create.
Optionally, you can specify the schema owner as
The following will create a new schema called
hrdbo and the default user
dbo will be the schema owner.
CREATE SCHEMA hrdbo
The following will create a new schema called
hrdbo with a user
Steve as the schema owner.
CREATE SCHEMA hrdbo AUTHORIZATION Steve
The above will create a new schema
hrdbo. You can see this in SSMS under Security -> Schemas folder, as shown below.
After you create a schema, you can create objects under this schema and grant permissions to other users.
For example, the following creates a new table under
CREATE TABLE hrdbo.Consultant ( ConsultantID int, FirstName nvarchar(50) NOT NULL, LastName nvarchar(50) NOT NULL );
The above will create a new table
Consultant in the
hrdbo schema, as shown below.
Use the ALTER SCHEMA statement to transfer database objects from one schema to another schema in the same database.
ALTER SCHEMA <schema_name> TRANSFER [entity_type::]securable_name;
In the above ALTER SCHEMA syntax:
schema_nameis the name of a schema in the current database, into which the securable (table, view, stored procedure, etc) will be moved.
entity_typecan be Object, Type or XML Schema Collection.
securable_nameis the name of db object (table, view, stored procedure, etc.) to be moved into the specified
The following transfers the
Employee table from the default
dbo schema to
ALTER SCHEMA hrdbo TRANSFER OBJECT::dbo.Employee;
The following shows the
Note: You can use ALTER AUTHORIZATION statement to change the owner of the schema as shown below:
ALTER AUTHORIZATION ON SCHEMA :: hrdbo TO dbo
Delete a Schema
DROP SCHEMA deletes a schema from the database. The schema that is being dropped must not contain any database objects. If the schema contains objects, the DROP statement fails.
DROP SCHEMA [IF EXISTS] schema_name
The following statement deletes the
hrdbo schema provided no objects in that schema.
DROP SCHEMA IF EXISTS hrdbo
Create Schema Using SSMS
A schema can also be created in SSMS tool.
Step 1: Open SSMS and connect to the database.
Step 2: In the Object Explorer, expand the
Databases folder and expand the instance of the database where you want the new schema to be created.
Step 3: Right-click on the
Security folder and select New -> Schema, as shown below.
This will open the
Schema New dialog box as below. On the General tab, enter the new schema name and Schema owner, as shown below.
The Schema Owner can be the name of the database user or role which will own the schema. Or to select a User/Role, click on the Search button.
In the Search Roles or User dialog box, click on the Browse button and select a User to whom you want to make as the owner of the Schema. Click Ok.
Step 4: Click on the Permissions page to see the lists of all the securables and the permissions on those securables that can be granted, as shown below.
Step 5: In the
Extended Properties tab, you can add customized properties to store additional information. Every extended property has a user-defined name and value. It is used for adding comments for a database object, as shown below.
Extended properties can be used to store version information, tooltips for user interfaces, data lineage information, descriptions of columns, and many more.
Step 6: Click OK to create the schema and save it.
Step 7: Refresh the Database instance tab. The new schema is created under the Security -> Schema folder, as shown below.
Thus, you can create, edit, delete database schemas using T-SQL and SSMS.