A Basic Walkthrough of SQL Schema

Introduction

The first time I heard and noticed schema back in 2005, I completely ignored it because I’m 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?

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

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.

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

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.

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.

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.

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.

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.

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 follow/subscribe, bookmark, like, and/or comment. Cheers! And Thank you!