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.
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 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 SCHEMA [MY_SCHEMA_3] AUTHORIZATION JIN_USER;
GO
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!