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?
- 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?
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
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
information_schema schemas, to name a few. By default, the newly created object uses the
dbo schema, while other system objects use the
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
How to Create a Schema?
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
CREATE LOGIN JIN_USER WITH PASSWORD = '123456'; GO CREATE USER JIN_USER FOR LOGIN JIN_USER; CREATE SCHEMA MY_SCHEMA2 AUTHORIZATION JIN_USER;
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
How to Drop Schema?
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
To get rid of the error and execute it successfully. We need to remove the referenced object, 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 getting rid of the schema. In this case, we have dropped the tables
CUSTOMERS. And finally, we have dropped the related schema, which in this case is
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!