The article describes the basics of a database schema, the advantages of using schemas, and covers how to create, modify, and delete them in the SQL Server database.
Prior to developing a database, a developer should thoroughly think of the database layout and objects to be included and how they should be organized logically. This is where they start creating a database schema that helps visualize and structure the SQL Server database and its relationships before implementing them.
Concept of SQL Server schemas
A database schema is a collection of logically connected database objects and elements, such as tables, stored procedures, views, functions, triggers, indexes, etc. The schema describes how data should be organized and how the table relationships should be built within a particular database.
In schemas, you can grant access rights and permissions, manage security, create, modify, or drop databases, and manipulate data. A user who is associated with a schema is called a schema owner, and you can create either one or multiple schema owners. A schema is the owner of database objects and can be associated only with one database. However, the database can have multiple schemas.
In SQL Server, schemas can be logical that explain how the data can be organized in tables and physical – explaining how data can be stored.
By default, SQL Server provides dbo, guest, sys, and INFORMATION_SCHEMA. When creating a new user, dbo will be considered as the default schema for this user.
Advantages of using database schemas
Using a database schema goes along with the benefits that include:
- Allow several users per schema
- Use the same schema with different databases
- Move database objects between schemas quickly
- Control access and apply user permissions to protect database objects efficiently at a schema level
- Remove a user without impact on user-dependent database elements
- Manage database objects in logical groups
- Transfer ownership between schemas
To move on, let’s take a closer look at how to retrieve schemas along with their owners, as well as how to create, alter, and drop a SQL database schema.
Get a list of schemas and their owners in a database
To retrieve all schemas and their respective owners from the current database ordered by the schema name, execute the following query by using sys.schemas:
SELECT
s.name AS schema_name,
u.name AS schema_owner
FROM
sys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY
s.name;The output will list all database schemas in the current database.
Create a schema in the current database
Now, let’s see how to create a new schema in the current SQL Server database using the CREATE SCHEMA statement. For example, we want to create the employment schema in the AdventureWorks2019 database.
CREATE SCHEMA employment;
GOIn the output, the new schema will be displayed in the list of available schemas for the current database.
Create a table within a schema
After we created the schema, we can try to add a new table (for example, vacancies) to the employment schema. For this, use the CREATE TABLE statement.
CREATE TABLE employment.vacancies(
vacancy_id INT PRIMARY KEY IDENTITY,
description VARCHAR(200),
created_at DATETIME2 NOT NULL
);In addition, other database objects can be created using the corresponding CREATE statements. If you do not specify a schema, SQL Server will use the default schema.
Keep in mind that database objects cannot be created in sys and INFORMATION_SCHEMA.
Now, let’s examine how to modify and drop a schema using dbForge Studio for SQL Server, an excellent SQL Server IDE that helps you deal with database development, administration, and management much faster, easier, more efficiently, and productively.
How to alter a schema
The following example shows how to define a new schema owner from the existing owners. For this, open dbForge Studio for SQL Server. In Database Explorer, right-click the schema you want to alter and select Edit Schema.
In Security Manager that opens, change the owner and click Save.
With the help of the ALTER SCHEMA statement, you can move securables from one schema to another within the same database. A securable is a database object with permissions and access granted, denied, and controlled by SQL Server.
The syntax is as follows:
ALTER SCHEMA target_schema_name
TRANSFER [ entity_type :: ] securable_name;where:
target_schema_name is the name of a schema in the current database, into which the securable will be moved.
entity_type is the class of the entity for which the owner is being changed. It can be Object, Type, or XML Schema Collection.
securable_name is the name of the object you want to move to the target schema.
When moving a stored procedure, function, view, or trigger, they should be dropped and recreated in the target schema. For these objects, the ALTER SCHEMA statement is not recommended to use.
When moving a table or synonym referenced in another database object definition, they should be manually modified to define the target schema name.
How to drop a schema
The following example shows how to drop a schema with the help of dbForge Studio for SQL Server. For this, in Database Explorer, right-click the schema you want to drop and select Delete. In the pop-up message that opens, confirm the deletion by clicking OK.
If there are objects referenced to the schema, first, you should delete the objects and then drop the schema.
Also, you can drop the schema using the DROP SCHEMA statement. The syntax is as follows:
DROP SCHEMA [IF EXISTS] schema_name;Conclusion
In the article, we have examined the definition of a SQL Server schema, the benefits of its usage, and provided the schema tutorial about how to create, alter, and drop the schema using the SQL Server statements and dbForge Studio for SQL Server. In addition, read the article to learn how to connect to a SQL Server database.
Download a 30-day free trial version of dbForge Studio for SQL Server to evaluate the cutting-edge features of the tool.
Besides, you can use our indispensable dbForge Schema Compare for SQL Server tool to compare and synchronize schemas quickly and easily.
