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
  • INFORMATION_SCHEMA

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 s.name AS schema_name,
u.name AS schema_owner
FROM sys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY s.name;

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.

CREATE SCHEMA HR AUTHORIZATION dbo;

GO

CREATE SCHEMA Admin AUTHORIZATION dbo;

Go

CREATE SCHEMA Fin AUTHORIZATION dbo;

GO

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

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

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].

CREATE TABLE HR.TableA
(
ID int identity(1,1) PRIMARY KEY,
[Name] varchar(100)
)
CREATE TABLE [Admin].TableA
(
ID int identity(1,1) PRIMARY KEY,
[Name] varchar(100)
)
CREATE TABLE [Fin].TableA
(
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
s.name AS SchemaName,
t.name AS TableName
FROM sys.tables t
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.name ='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.

CREATE TABLE TableA
(
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.

CREATE PROCEDURE HR.GetEmpData
AS
BEGIN
SELECT * FROM [HR].[TableA]
END

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.

CREATE SCHEMA [ORG]
GO
ALTER SCHEMA ORG TRANSFER HR.TableA

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

ALTER SCHEMA ORG TRANSFER HR.GetEmpData

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 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.

About the Author

Rajendra Gupta

Rajendra Gupta is a MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" in 2020 and 2021 at SQLShack.

Related Articles