A Walkthrough of SQL Schema

A Walkthrough of SQL Schema

   This article gives an overview of SQL Schema and its usage in SQL Server.

Introduction to Schema

A SQL database contains multiple objects such as tables, views, stored procedures, functions, indexes, triggers. We define SQL Schema as a logical collection of database objects. A user owns that owns the schema is known as schema owner. It is a useful mechanism to segregate database objects for different applications, access rights, managing the security administration of databases. We do not have any restrictions on the number of objects in a schema.

Starting from SQL Server 2005, we have different meanings of user and schema. Now, the database object owner is a schema, and we define schema owners. We can have a single or multiple schema owners. It provides the following benefits:

• We can quickly transfer ownership of a SQL schema to another user
• We can share a schema among multiple users
• It allows you to move database objects among the schemas
• We get more control over database objects access and security

We define default SQL Schema for a database user in the create database user window: 

 

 If we do not define any default schema for a user, SQL Server assumes dbo as the default schema. We can verify the default schema for a user using the following system function: 

 

 

Retrieve all schema and their owners in a database

We can query sys.schemas system table to find out schema in a database and their owners: 

SELECT s.name AS schema_name,

       s.schema_id,

       u.name AS schema_owner

FROM sys.schemas s

     INNER JOIN sys.sysusers u ON u.uid = s.principal_id

ORDER BY s.name;

In the following screenshot, we can see master database schema and their owners: 

 

SQL Schema is a database entity, and you might get a different result of the query in a user database. For example, the AdventureWorks database shows the following schema information:

 

Note: SQL Server does not allow users to create objects in sys and INFORMATION_SCHEMA. It is used for storing internal system objects.

 

 

Conclusion

A SQL schema is a useful database concept. It helps us to create a logical grouping of objects such as tables, stored procedures, and functions.