A Basic Walkthrough of SQL Schema

Introduction

The first time I heard and noticed schema back in 2005, I completely ignored it because I was comfortable using dbo as my default schema. But, as time went by, I decided to understand and implement it in my database projects until I became more comfortable using it. Thus, I’ll be sharing some of my views about the SQL Server’s schema. Ok, then let’s get started.

Table of Contents

• What is a Database Schema?
• Why Do We Need Schemas?
• What are the Built-in Schemas in SQL Server?
• How to Create a Schema?
• How to Create Objects Under a Schema?
• How to Alter Schema?
• How to Drop Schema?
• Summary

What is a Database Schema?

• Database schema acts as a container of objects such as tables, views, stored procedures, etc.
• Suppose you are familiar with a namespace in other programming languages; it is similar to a schema. Moreover, to prefix to the object name, a schema is used. For example, suppose you have a table named Employee in a schema of HR. Then the schema-qualified object name is HR.Employee.

Why Do We Need Schemas?

Most of us are most likely familiar with the default and built-in schema, the dbo. Once you have created an object without a schema, dbo becomes the object’s default schema. This practice is considered a bad one from a manageability perspective. That’s why it is accepted to logically separate objects into groups or schemas.

What are the Built-in Schemas in SQL Server?

By default, SQL Server provides several schemas that exist in a newly created database, such as dbo, sys, guest, and information_schema schemas, to name a few. By default, the newly created object uses the dbo schema, while other system objects use the sys and information_schema schemas.

Let us see how we can list the schemas of a database.

SELECT 
	schema_id [Id],
	name [Schema Name]
FROM SYS.SCHEMAS
ORDER BY name

Output

How to Create a Schema?

The CREATE SCHEMA statement is straightforward and, of course, creates a new schema in your selected database.

Let us see an example below:

--CREATE A NEW SCHEMA 
CREATE SCHEMA MY_SCHEMA; 
GO

Let’s create a new schema that includes the AUTHORIZATION keyword.

CREATE LOGIN JIN_USER
	WITH PASSWORD = '123456';
GO

CREATE USER JIN_USER FOR LOGIN JIN_USER;

CREATE SCHEMA MY_SCHEMA2 AUTHORIZATION JIN_USER;

Output

How to Create Objects Under a Schema?

Once you have created a schema within your database, you can now create objects for that schema. Just like in our example, we have created a new schema named MY_SCHEMA2. And, now will show how to create a table under that schema.

Let us see the example below:

USE [EMPLOYEE_DEPARTMENT_DB];

CREATE TABLE [MY_SCHEMA2].ORDERS
(
	OrderId int NOT NULL
);
GO

How to Alter Schema?

When it comes to altering a schema, the first thing that comes to mind is renaming the schema-name. However, you can’t directly rename a schema, but you can move an object’s schema to a new one using the ALTER SCHEMA statement.

Let us see the example below:

USE [EMPLOYEE_DEPARTMENT_DB];
GO 

--create a new schema
CREATE SCHEMA [MY_SCHEMA_3] AUTHORIZATION JIN_USER;
GO

-- transfer the old schema to the newly created one
-- see the examples below. 

ALTER SCHEMA [MY_SCHEMA_3] TRANSFER  [MY_SCHEMA2].CUSTOMERS;
GO

ALTER SCHEMA [MY_SCHEMA_3] TRANSFER	[MY_SCHEMA2].ORDERS;
GO

Output

How to Drop Schema?

The DROP SCHEMA statement allows you to delete a schema from a database. However, one thing needs to be aware of; the schema must not use any objects like a table to execute this statement successfully.

Let us first see dropping a schema that doesn’t have any references to any objects.

USE [EMPLOYEE_DEPARTMENT_DB];
GO 

CREATE SCHEMA MY_SCHEMA_WITHOUT_REFERENCES; 
GO

DROP SCHEMA MY_SCHEMA_WITHOUT_REFERENCES;
GO 

Let us see how to drop a schema that has a reference to a table object.

When we look at the screenshot below as our example, we can see two tables under the MY_SCHEMA_3 schema.

Once we have decided to drop the MY_SCHEMA_3 schema, we’ll eventually get an error because we need to remove the references first. Let us see it first before we release the references.

USE [EMPLOYEE_DEPARTMENT_DB];
GO

DROP SCHEMA [MY_SCHEMA_3];
GO 

Output

To get rid of the error and execute it successfully, we need to remove the referenced object, the CUSTOMERS, and ORDERS table to drop the schema altogether.

See the updated script below.

USE [EMPLOYEE_DEPARTMENT_DB];
GO

DROP TABLE [MY_SCHEMA_3].[ORDERS];
DROP TABLE [MY_SCHEMA_3].[CUSTOMERS];

DROP SCHEMA [MY_SCHEMA_3];
GO 

As you can see, we need to drop the objects first, even before getting rid of the schema. In this case, we have dropped the tables ORDERS and CUSTOMERS. And finally, we have dropped the related schema, which in this case is MY_SCHEMA_3.

Summary

In this post, we have learned the basics of SQL Schema. Moreover, we have tackled a database schema to create, alter, and drop schemas. I hope you have enjoyed this article, as I have enjoyed writing it. Stay tuned for more. Until next time, happy programming! Please don’t forget to bookmark, rate, and/or comment. Cheers and thank you for reading!