What is a Schema in SQL Server ?
A Schema in SQL is a collection of database objects associated with a database. The username of a database is called a Schema owner. Schema always belong to a single database whereas a database can have single or multiple schemas. Also, it is also very similar to separate namespaces or containers, which stores database objects. It includes various database objects including your tables, views, procedures, index, etc. 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. 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:
SELECT SCHEMA_NAME();
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;
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.
Advantages of using Schema -
You can apply security permissions for separating and protecting database objects based on user access rights.
A logical group of database objects can be managed within a database. Schemas play an important role in allowing the database objects to be organized into these logical groups.
The schema also helps in situations where the database object name is the same. But these objects fall under different logical groups.
A single schema can be used in multiple databases.
The schema also helps in adding security.
It helps in manipulating and accessing the objects which otherwise is a complex method.
You can also transfer the ownership of several schemas.
The objects created in the database can be moved among schemas.
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
Steps in order to create a schema Using SQL Server Management Studio:
In object explorer, click on the databases folder.
Create the New database schema under database.
Right click Security folder, click New, select Schema.
Go on Schema-New dialog box, enter a specific name that you want to create for your new schema.
In the schema owner box, enter the name of the database user in order to own the schema. Click search, to open the Search Roles and User dialogue box.
Click OK.
How to create a Schema?
Syntax to create SQL:
CREATE SCHEMA [schema_name] [AUTHORIZATION owner_name]
[DEFAULT CHARACTER SET char_set_name]
[PATH schema_name[, ...]]
[ ANSI CREATE statements [...] ]
[ ANSI GRANT statements [...] ];
How to alter a Schema?
The schema in a database can be altered by using the alter schema statement. This statement is specifically used to rename a schema. The new owner must be a pre-existing user.
Syntax to alter a schema:
ALTER SCHEMA schema_name [RENAME TO new_schema_name] [ OWNER TO new_user_name]
Description :-
new_schema_name = new name of the schema
schema_name = existing schema
new_owner = new owner of the schema
How to drop a Schema?
In order to drop schema we use the following syntax:
DROP SCHEMA <schema name>
