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.

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;

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.

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.

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

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'

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

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.

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

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.

Visibility into it all.

Monitor your databases and resolve performance issues quickly.

Try Foglight

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.

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.

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

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.