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 share some of my views about the SQL Server's schema.

Ok, then, let's get started.

What is a Database Schema?

The database schema is 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 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 separate objects into groups or schemas logically.

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 creates a new schema in your selected database.

Let us see the example below.

--CREATE A NEW SCHEMA   
CREATE SCHEMA MY_SCHEMA;   
GO

Output

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. As in our example, we 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

Output

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 objects like a table to execute this statement successfully.

Let us first see dropping a schema with no 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 references 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 eliminate and execute the error, we must remove the referenced object, the CUSTOMERS, and the 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 removing 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. Many thanks; until next time, happy programming!