Azure SQL Database Schema - Complete tutorial

In this Azure SQL tutorial, I will explain what are schemas and how to manage them in an Azure SQL database.

I will explain how you can create, view, alter and delete a schema in the database. I will create and explain multiple examples of managing schemas in an Azure SQL database.

What is schema in Azure SQL database?
Create schema Azure SQL database
Azure SQL create schema example
View list of schemas in Azure SQL database
Azure SQL create schema if not exists
Create table under schema Azure SQL database
Change user default schema Azure SQL
Change schema of a table in Azure SQL
Change schema owner Azure SQL
Azure SQL drop schema
Azure SQL drop schema if exists

Table of Contents

What is schema in Azure SQL database?

In any database, there are a large number of objects of various types such as tables, views, stored procedures, functions, triggers, etc. It is easy to deal with these objects if they are managed in an organized way.

To organize the objects, the concept of schemas was introduced.

A schema is a logical collection of objects in the database. We create multiple schemas in the database and create objects under these schemas. Thus, the objects can be accessed in some particular ways as per the schema definition.

A schema is owned by a principal such as any user or role. Thus, the owner of the schema is responsible for managing the objects under the schema. Also, the schema can be shared by multiple users.

Now let us see how to create a new schema in an Azure SQL database.

Create schema Azure SQL database

To create a schema in an Azure SQL database, we use the CREATE SCHEMA statement. The general syntax of the CREATE SCHEMA statement is:

CREATE SCHEMA <schema name>
	AUTHORIZATION <schema owner name>
	<new schema objects or permissions>

In the above syntax, you can see how to create a schema with an owner and new schema objects. You can create a new table or view under the new schema or specify the permissions while creating the new schema.

Now let us see some examples of creating a new schema in an Azure SQL database.

Migrate SQL Server to Azure SQL database

Azure SQL create schema example

Let us see how we can create a schema in the Azure SQL database with the CREATE SCHEMA statement.

CREATE SCHEMA Production AUTHORIZATION TestUser

The above statement will create a new schema named Production.

View list of schemas in Azure SQL database

To view the list of all schemas in an Azure SQL database, you can query the sys.schemas system catalog view. The sys.schemas view stores all the schemas along with the principal IDs of the owner of the schemas.

You can query the sys.schemas view with a SELECT statement as:

SELECT * FROM sys.schemas

View schemas in Azure SQL database — List of all schemas in the database

You can query this view in other ways also. For example, if we want to get the information of a schema with its name, we can write the T-SQL query as:

SELECT * FROM sys.schemas WHERE name = 'Production'

View list of schemas in Azure SQL database — Find schema information by name

Also, if you want to view the name of the owner of the schema, you can use an inner join between the sys.schemas and the sys.sysusers views as:

SELECT sch.name AS [Schema Name],
    sch.schema_id AS [Schema ID],
    users.name AS [Schema Owner]
FROM sys.schemas sch
    inner join sys.sysusers users
        ON users.uid = sch.principal_id
ORDER BY sch.name

list schemas in Azure SQL database — List of schemas and their owners

Hence, these were some ways that you can use to view your schemas in the Azure SQL database.

How to create SQL authentication user in Azure SQL database

Azure SQL create schema if not exists

If we try to create a schema that is already in the database, we encounter an error mentioning that the schema is already available in the Azure SQL database.

However, we can put a validation check before creating a database schema. The result of this approach is that we will not encounter any error if the schema is already available in the database.

I have created an example in which the CREATE SCHEMA statement will run only if a schema with the same name is not available in the database.

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'MySchema')
BEGIN
	EXEC('CREATE SCHEMA MySchema AUTHORIZATION TestUser')
END

Azure SQL create schema if not exists — Azure SQL database schema

You might have noticed one thing that I have used the EXEC() function to execute the CREATE SCHEMA statement instead of directly writing it inside the true block of the IF-ELSE statement.

This is because, while executing the CREATE SCHEMA statement, it must be the only statement in the T-SQL query batch. This means we cannot execute the IF-ELSE statement and CREATE SCHEMA statement in a single T-SQL batch.

Thus, when we used the EXEC() function to execute the CREATE SCHEMA statement, the statement got executed in a separate batch.

Read Pause and resume Azure SQL database

Create table under schema Azure SQL database

There are two ways in which you can create a table under a schema in an Azure SQL database.

The first way is to create a table when a new schema is created. For example, the below T-SQL code will create a new schema with the name Production whose owner will be a user named TestUser.

Also, a new table will be created with the name DemoTable belonging to the newly created schema.

CREATE SCHEMA Production AUTHORIZATION TestUser
	CREATE TABLE DemoTable
	(
	Column1 int,
	Column2 nvarchar(20)
	)
GO

Now if you refresh the tables list of your Azure SQL database, you can see that a new table is created under the schema named Production.

Create table under new schema Azure SQL database — New table created under the new schema

The second way to create a table under a schema is to simply mention the schema name before the table name while creating the table.

CREATE TABLE [schema name].[table name]
(
  <Table Definition>
)

For example, If I want to create a table named TestTable under the Production schema, I will execute the T-SQL query as:

CREATE TABLE Production.TestTable
	(
	[First Name] nvarchar(20),
	[Last Name] nvarchar(20),
	Age int,
	Stream nvarchar(20),
	Email nvarchar(20),
	[Contact Number] nvarchar(12),
	)
GO

Now if you again refresh the tables in your Azure SQL database, you can see that a table is created with the same name under the Production schema.

Create table under schema Azure SQL database — Create a table under a database schema

Thus, you might have learned how you can create a new table under a database schema in an Azure SQL database.

Read Reset Password Azure SQL database

Change user default schema Azure SQL

When you create a new user in an Azure SQL database, you need to specify the default schema for a user. This means when a user will create a new object in the database, it will be created under the user’s default schema.

If you don’t specify a default schema while creating a user, dbo will become the default schema for that user.

In case you want to change the default schema of the user, you can use the ALTER USER command. The general syntax for changing the default schema of a user is:

ALTER USER <username> WITH DEFAULT_SCHEMA = <new default schema name>;

For example, in my Azure SQL database, I have created a user without mentioning a default schema. As a result, the default schema is dbo.

Now if I want to change the default schema to Sales for the user named TestUser, I will execute the below T-SQL code:

ALTER USER TestUser WITH DEFAULT_SCHEMA = Sales;

You can verify in the sys.database_principals system catalog view that the default user schema is changed by executing the below T-SQL query:

SELECT name, default_schema_name 
FROM sys.database_principals 
WHERE type = 'S' and name

Change user default schema Azure SQL — Changed the default schema of a user

You can see that the default schema name is changed to Sales for the user TestUser. In this way, you can use the ALTER USER statement to change the default schema name of a user in an Azure SQL database.

Read Cannot bulk load because the file could not be opened. operating system error code (null). azure

Change schema of a table in Azure SQL

In this section, I will explain how you can change the ownership of an existing object under a schema in an Azure SQL database.

We use the ALTER SCHEMA statement to change the ownership of an object in the database.

The general syntax of the ALTER SCHEMA statement is:

ALTER SCHEMA <schema name> TRANSFER <object name>;  
GO

The schema name will be the name of the schema under which you want to move the object. The object name is the full name of the object that you want to move.

For example, we created the table TestTable under the Production schema in the earlier examples.

Now if we want to move this table under the dbo schema i.e. change the ownership of the table from Production to dbo schema, we will write the T-SQL query as:

ALTER SCHEMA dbo TRANSFER Production.TestTable;  
GO

After refreshing the tables in the Azure SQL database, you can see that the ownership is changed to dbo.

Change schema Azure SQL — Ownership changed

Thus, you might have learned how you can change the ownership of an object in the Azure SQL database.

Also read, Azure SQL database query history

Change schema owner Azure SQL database

You may face a scenario when you want to change the owner of a schema i.e. assign the ownership of a schema to a new database principal, in an Azure SQL database.

In such a scenario, you can use the ALTER AUTHORIZATION statement. The general syntax of this statement is:

ALTER AUTHORIZATION ON SCHEMA::<schema name> TO <database principal>;
GO

The database principal can be any user or any role.

For example, the below T-SQL query will change the owner of a schema named Production to the user named read_only_user.

ALTER AUTHORIZATION ON SCHEMA::Production TO read_only_user;
GO

To confirm if the operation is successful, you can view the list of schemas in the database using the below T-SQL query:

SELECT sch.name AS [Schema Name],
    sch.schema_id AS [Schema ID],
    users.name AS [Schema Owner]
FROM sys.schemas sch
    inner join sys.sysusers users
        ON users.uid = sch.principal_id
ORDER BY sch.name

Change schema owner Azure SQL — Changed the owner of a schema

You can see that the owner is changed to the user named read_only_user.

Hence, in this way you can change the owner of a schema in an Azure SQL database.

Read Read only replica Azure SQL

Azure SQL drop schema

To drop a database schema in your Azure SQL database, you can use the DROP SCHEMA statement. The general syntax for using this statement to drop a schema is:

DROP SCHEMA <schema name>

But before dropping a schema, you need to make sure that there is no object under a schema. This means if you have a table or stored procedure under a schema, you cannot delete that schema.

Thus, to drop the schema, you need to drop the schema objects first. For example, if I want to delete a schema named Production, I will first delete the objects under this schema and then execute the below T-SQL query:

DROP SCHEMA Production

Azure SQL drop schema — Dropping a schema

If the query is executed successfully, your schema will be dropped.

In this way, you can drop a schema in an Azure SQL database.

Read Bulk loading data to Azure SQL

Azure SQL drop schema if exists

If you try to drop a schema that does not exist in the Azure SQL database, you will face an error. Thus, you may want a mechanism that will check if the schema exists in the database or not.

After that, if the schema already exists, only then it will be deleted. The benefit of using this technique is that the DROP SCHEMA statement will get executed only if the schema exists in the database. As a result, you will not face any errors.

To implement this, you can write the IF EXISTS clause after the DROP SCHEMA statement as follows:

DROP SCHEMA IF EXISTS <schema name>

For example, if I want to delete the schema named Production, I will execute the below T-SQL query:

DROP SCHEMA IF EXISTS Production

azure SQL drop schema if exists — Using IF EXISTS with DROP SCHEMA

Hence, in this way, you can put a validation before dropping a schema.