Schema in SQL

A schema in a SQL database is a collection of logical structures of data. The schema is owned by a database user and has the same name as the database user. From SQL Server 2005, a schema is an independent entity (container of objects) different from the user who creates that object. In other words, schemas are very similar to separate namespaces or containers that are used to store database objects. Security permissions can be applied to schemas hence schemas are an important tool for separating and protecting database objects on the basis of user access rights. It improves flexibility for security-related administration of the database.

User schema separation

Before SQL Server 2005, database object owners and users were the same things and database objects (table, index, view and so on) were owned by the user. In other words database objects were directly linked to the user and the user could not delete them without removing the database objects that were associated with the user. In SQL Server 2005, a schema separation is introduced, now the database object is no longer owned by a user, group or role. The schema can be owned by the user, group or role. The schema can have multiple owners. The schema ownership is transferable. Database objects are created within the schema. Now the user can be dropped without the dropping of the database object owned by the user. But the schema cannot be deleted if it contains a database object.

The following are advantages of user schema separation:

• The schema ownership is transferable.
• Database objects can be moved among the schemas.
• A single schema can be shared among multiple users.
• A user can be dropped without dropping the database objects associated with the user.
• Provides more control of access and level of access.

Default schema

The default schema is the first schema searched when resolving object names. The user can be defined within the default schema. Using the “SCHEMA_NAME” function we can determine the default schema for the database.

 The schema can be made the default for the user by defining DEFAULT_SCHEMA with CREATE USER or ALTER USER. If there is no default schema defined then SQL will assume “DBO” as the default schema. Note that there is no default schema associated with a user if the user is authenticated as a member of the group in the Windows operating system. In this case a new schema will be created and the name is the same as the user name.

 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 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, database objects 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:

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.

Let’s create a new table in the AdventureWorks database using the following query:

We did not specify any schema in the CREATE TABLE statement. It automatically uses dbo schema for the table because the current user default schema is dbo:

Specify default SQL schema while creating a new login

Let’s create a new SQL login and database user with a default schema other than dbo.

Go to Security and create a new login:

Provide the following inputs on the general page:

• Enter a SQL user name
• Choose SQL Server authentication method and enter a password for the user

Navigate to the User Mapping page and put a check on the AdventureWorks database:

Click on eclipse for default schema and browse available schema in the database. Let’s select schema [Person] as a default schema for this user:

Click OK and return to the user-mapping page. We can see that the new login is having access to the AdventureWorks database and its default schema is [Person]:

Enroll in SQL Server DBA Training to Build Skills & Advance Your Career

• Instructor-led Sessions
• Real-life Case Studies
• Assignments

Explore Curriculum

Click OK, and it creates the SQL login, database user in the AdventureWorks database. Connect to SQL instance with this login. Execute the create table statement without specifying a SQL Schema. We can see it creates the table in the default schema for the user, i.e. [Person]:

Alternatively, we can run the script by specifying a schema name.

Script to create a table in [Person] schema:

Script to create a table in the dbo schema:

Create a new SQL Schema in SQL Server

We can create schemas as per our requirements. We can create a schema in a specific database. Let’s create a new schema in the AdventureWorks database.

Expand the AdventureWorks database and go to Security:

Right-click on Schemas and click to New Schema:

Specify a new schema name and schema owner. You can search the schema owner from existing logins:

Instead of creating the schema, click on generate scripts to give you an equivalent T-SQL statement:

In this syntax, we can see that:

• [Customer] is the new schema name that we want to create in the AdventureWorks database
• We specify a schema owner using the AUTHORIZATION keyword in the CREATE SCHEMA command

Execute the command and refresh the schema list (right-click on schemas and refresh). You can see a new schema [Customer] on this list:

Note: Earlier, we created a demo user with a default schema [Person]. The demo user is the schema owner of the customer schema as well. If we connect to SQL instance using the demo user and create objects without specifying a schema name, it still takes default schema as [Person].

Advantages of using Schema

• Act as object protection tool: A schema can be a very effective object projection tool combined with the appropriate level of user permissions. A DBA can maintain control access to an object that would be very crucial.
• Managing a logical group of database objects within a database: Schemas allow database objects to be organized into a logical group. This would be advantageous when multiple teams are working on the same database application and the design team wants to maintain integrity of the database tables.
• Easy to maintain the database: A schema allows a logical grouping of the database objects, so the schema can help us in situations where the database object name is the same but falls in a different logical group.

How does MySQL Schema Work?

To work with MySQL database the first step is to create a schema. The command below shows the creation of it.

Creation:

Creating a schema is very simple just like the creation of a database.

Create a schema name;

To display a List of Schemas Available the Below Command is:

Create a Table in Schema:

Removing Metadata:

Searching Schemas:

Finding a database containing a specific table in MySQL.