Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

A Basic Walkthrough of SQL Schema

5.00/5 (2 votes)
7 Dec 2020CPOL4 min read 6.5K  
This article shows you the basics of SQL Schema using the DDL statements.
You will use DDL statements to learn the basics of SQL Schema. In this post, we tackle a database schema to create, alter, and drop schemas.

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?

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

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

Output

SQL_Server_List_Of_Schema

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:

SQL
--CREATE A NEW SCHEMA 
CREATE SCHEMA MY_SCHEMA; 
GO

Image 2

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

SQL
CREATE LOGIN JIN_USER
	WITH PASSWORD = '123456';
GO

CREATE USER JIN_USER FOR LOGIN JIN_USER;

CREATE SCHEMA MY_SCHEMA2 AUTHORIZATION JIN_USER;

Output

Create_Schema_Statement_With_Authorization_Keyword

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:

SQL
USE [EMPLOYEE_DEPARTMENT_DB];

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

Create_Schema_Statement

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:

SQL
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

Image 5

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.

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

Image 6

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.

SQL
USE [EMPLOYEE_DEPARTMENT_DB];
GO

DROP SCHEMA [MY_SCHEMA_3];
GO 

Output

Image 7

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.

SQL
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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)