SQL Server Schema

Schema can be thought as namespace or container which contains database objects such as tables, views, indexes, stored procedures etc. Database objects can be moved into schema and can be removed from the schema. Schema is database object which is owned by a database user.

Schema is an independent entity in SQL server. It means that a user can be authorized for a schema and the rights can be revoked as well. The ownership of schema is transferable as well as distributable.

A schema can be owned by more than one user at the same time. Schemas provide security features to the database objects and this is the primary importance of schema. We cannot access database objects of a schema unless we are authorized for the schema of the objects. In SQL server database, there are two types of schemas.

In-Built Schemas and User-Defined Schemas

The in-built schemas in SQL Server are SYS. We can create user defined schema as well. A database can have more than one schema. Basically, schema is used to partition the database objects into different namespaces. Schemas provide security to the database and in the object explorer, they are under the Security.

Creating Schema with script

Each schema object has a unique name and schema id. Creating a schema is very easy. We use the CREATE SCHEMA <schema name> to create a schema. We also can provide the owner name to a schema.

CREATE SCHEMA <schema name> AUTHORIZATION <owner name>

We cannot delete a schema if it contains any database object.

Creating Schema with GUI

In the Object Explorer, expand the database folder in which you want to create schema. Then right-click the Security folder, point to New, click ‘Schema’.

In the Schema - New dialog box, on the General page, enter a name for the new schema in the Schema name box. In the Schema owner box, enter the name of a database user or role to create schema.

If the Schema owner name is not correct, error will be thrown. For example, Ajeet is not the correct owner name and so error is thrown as shown below.

We can use DBO as the default owner.

Remark: before 2005, schema was tied with the owner and deleting owner led to deletion of schema as well. The database objects owner and database user were same. Now schema is independent of user, group or role.

Creating Table in Schema

Let’s suppose that schema named ‘MySchema’ is created. To create a table belonging to this schema, we use fully qualified name of table as shown below.

CREATE TABLE MySchema.MyTable1
(
Id int,
City varchar(20)
)

If you create the table without schema name then default schema will be dbo. For the following script, the schema will be dbo.

CREATE TABLE MyTable1
(
Id int,
City varchar(20)
)

SYS schema

We can query different objects of SYS as follows:

• SELECT * FROM sys.all_objects
• SELECT * FROM sys.schemas
• CREATE SCHEMA MySchema AUTHORIZATION Ajeet
• CREATE TABLE Mobiles (ModelID int, Price int, Model varchar(20))
• GRANT SELECTONSCHEMA::MySchemaTOTom
• DENY SELECTONSCHEMA::MySchemaTOJohn;

Understanding information schema views

There are many information schema views available which can be queried to get information about the SQL Server system. SQL Server defines 17 information schema views in each database. Information schema view contains different information like tables, database, foreign key, primary key, check constraint, views, user define data types etc.

INFORMATION_SCHEMA.XXX where XXX stands for a system schema view e.g. tables, columns, domains, views etc.

SELECT * FROM INFORMATION_SCHEMA.Tables

The above query will return the entire table available in the current database.

In the next post we will learn about Transact-SQL.