Using Database Schemas in SQL Server

This article will explain what database schemas are, their advantages and how to create and use schemas in SQL Server.

In a relational database management system such as SQL Server, the database contains various objects. These can be tables, stored procedures, views and functions. In a database, the schema refers to the logical collection of database objects. You can use schemas to segregate objects depending on the application, access rights and security.

SQL Server schemas

SQL Server provides the following built-in logical schemas:

  • dbo
  • sys
  • guest

Every SQL Server schema must have a database user as a schema owner. The schema owner has full control over the schema. You can also change the schema owner or move objects from one schema to another.

SQL Server schemas provide the following benefits:

  • Provides more flexibility and control for managing database objects in logical groups
  • Allows you to move objects among different schemas quickly
  • Enables you to manage object security on the schema level
  • Allows users to manage logical groups of objects within a database
  • Allows users to transfer ownership among various schemas

Suppose for your organization’s database, you want to group objects based on departments. For example, the tables and stored procedures for the HR department should be logically grouped in the [HR] schema. Similarly, finance department tables should be in the [Fin] schema. Each schema (logical group) contains SQL Server objects such as tables, stored procedures, views, functions, indexes, types and synonyms.

 schema employee database example

Note: The schema is a database-scoped entity. You can have the same schema in different databases of a SQL Server instance.

By default, SQL Server uses [dbo] schema for all objects in a database. We can query SCHEMA_NAME() to get the default schema for the connected user.

SELECT SCHEMA_NAME() AS defaultschema;

Database schema query SCHEMA_NAME 

Listing all database schemas in the current database

You can get a list of the schemas using an SSMS or T-SQL query. To do this in SSMS, you would connect to the SQL instance, expand the SQL database and view the schemas under the security folder.

 Listing database schemas in current databases

Alternatively,  you could use the sys.schemas to get a list of database schemas and their respective owners.

SELECT AS schema_name, AS schema_owner
FROM sys.schemas s
     INNER JOIN sys.sysusers u ON u.uid = s.principal_id

Creating a SQL Server schema using CREATE SCHEMA

To create a new SQL Server schema, we use the CREATE SCHEMA t-SQL statement. Its syntax is shown below.

CREATE SCHEMA <schema_name>
AUTHORIZATION <owner_name>

  • Schema_name: This is the schema that we want to create
  • Authorization: This is the schema owner name

The script to create schemas [HR], [Admin] and [Fin] with [dbo] schema owner is shown below.







You can refresh the database and view the newly created schema as shown below.

 Creating SQL Server Schemas

Creating a new table within a schema

To create objects such as a table, we need to specify the schema name in which the object will be created. For example, the following script creates [TableA] in different schemas [HR], [Admin] and [Fin].

     ID int identity(1,1) PRIMARY KEY,
     [Name] varchar(100)
     ID int identity(1,1) PRIMARY KEY,
     [Name] varchar(100)
     ID int identity(1,1) PRIMARY KEY,
     [Name] varchar(100)

Creating tables for SQL Server schemas

You can join sys.tables and sys.schema system tables to list the table name with their schemas. For example, the below query returns [TableA] with its schema.

SELECT AS SchemaName, AS TableName 
FROM sys.tables t 
     INNER JOIN sys.schemas s 
     ON t.schema_id = s.schema_id 
WHERE ='TableA'

 creating SQL Server schema table example

If you do not specify a schema while creating the object, SQL Server uses the default schema. For example, the below script creates a TableA in the dbo schema.

     ID int identity(1,1) PRIMARY KEY,
     [Name] varchar(100)

Creating SQL Server schemas from TABLE A 

Note: You can create a table with a similar name in the different schema of a database.

You are required to specify the table schema in order to fetch data from the correct schema. For example, as shown above, we have [TableA] in all schemas. Therefore, if you select records directly without specifying the schema, it looks for an object in the default DBO schema. Therefore, always specify the schema name such as SELECT * FROM HR.TableA for data retrieval or performing any operations.

Creating a stored procedure within a schema

Similarly, you can create objects such as stored procedures in specified schemas. For example, the below script creates a SP in the HR schema.


Transferring the object to a different schema

At some point, you might get a requirement to move the object to a specific schema. For example, suppose you created a new schema [Org], and you want your [HR].[TableA] to move from [HR] schema to [Org] schema.

In this case, you can use the Alter SCHEMA command with the following syntax.

ALTER SCHEMA target_schema_name
     TRANSFER [ entity_type :: ] securable_name;

The below script transfers object [HR].[TableA] to the [Org] schema.


Now, let’s transfer the stored procedure [HR].[GetEmpData] to the [Org] schema.


Once you execute the script, it shows the stored procedure in the [Org] schema.

 Database schema script execution

However, inside the stored procedure, the code still refers to the [HR].[TableA] schema.

 Moving stored procedures

Therefore, you should not move stored procedures, functions or views using the ALTER SCHEMA function as it might have references for the objects in the definition. Instead, you can drop, create or alter the procedure as shown below.

 Using the ALTER SCHEMA function

Dropping a schema

You can drop a schema in a SQL Server database, but the schema should not hold any objects. For example, if I try to drop the [Org] schema, it gives an error that you cannot drop the schema because the object GetEmpData is referencing it.

Dropping a database schema

Therefore, you can either transfer the object to a different schema or drop the objects first. For example, let us drop GetEmpData stored procedures and then try to drop the schema. We again got an error because we have [TableA] in the [Org] schema.

Dropping or moving objects schema example 

Once we dropped or moved all objects in the database schema, you can then drop the schema.

 Dropping schemas example

Note: You cannot drop system schemas such as dbo, information_schema, sys.

Advantages of using database schemas

  • Database schemas provide us with the flexibility to create logical object groups in a database. If multiple teams are working with the same database, we can design various schemas to segregate their objects.
  • Database schemas help database professionals manage access, as you can control access to users for their respective schema(s) instead of providing access to the database as whole.
  • You can more efficiently manage databases because it allows the same objects in multiple schemas to appear as a different logical group.
  • You can quickly move objects within different schemas.
  • The schema ownership can be assigned with any database principal or roles and ownership can be transferred as well.
  • It provides an additional layer of security since you need to know the correct object schema to query or manipulate data. You can also control access on schema and schema-owned objects.
Related Content