DDL Schema-Related Statements

There are three Transact-SQL schema-related statements:

• CREATE SCHEMA
• ALTER SCHEMA
• DROP SCHEMA

The following subsections describe in detail these statements.

CREATE SCHEMA

Example 12.5 shows how schemas can be created and used to control database security.

USE sample;
GO
CREATE SCHEMA my_schema AUTHORIZATION peter
GO
CREATE TABLE product
	(product_no CHAR(10) NOT NULL UNIQUE,
	product_name CHAR(20) NULL,
	price MONEY NULL);
	
GO
CREATE VIEW product_info
	AS SELECT product_no, product_name
		FROM product;
GO
GRANT SELECT TO mary;
DENY UPDATE TO mary;Code language: PHP (php)

Example 12.5 creates the my_schema schema, which comprises the product table and the product_info view. The database user called peter is the database-level principal that owns the schema. (You use the AUTHORIZATION option to define the principal of a schema. The principal may own other schemas and may not use the current schema as his or her default schema.)

The CREATE SCHEMA statement can create a schema, create the tables and views it contains, and grant, revoke, or deny permissions on a securable in a single statement. As you already know, securables are resources to which the SQL Server authorization system regulates access. There are three main securable scopes: server, database, and schema, which contain other securables, such as logins, database users, tables, and stored procedures.

The CREATE SCHEMA statement is atomic. In other words, if any error occurs during the execution of a CREATE SCHEMA statement, none of the Transact-SQL statements specified in the schema will be executed.

Database objects that are created in a CREATE SCHEMA statement can be specified in any order, with one exception: a view that references another view must be specified after the referenced view.

A database-level principal could be a database user, role, or application role. (Roles and application roles are discussed in the “Roles” section later in the chapter.) The principal that is specified in the AUTHORIZATION clause of the CREATE SCHEMA statement is the owner of all objects created within the schema. Ownership of schema-contained objects can be transferred to any other database-level principal using the ALTER AUTHORIZATION statement.

The user needs the CREATE SCHEMA permission on the database to execute the CREATE SCHEMA statement. Also, to create the objects specified within the CREATE SCHEMA statement, the user needs the corresponding CREATE permissions.

ALTER SCHEMA

The ALTER SCHEMA statement transfers an object between different schemas of the same database. The syntax of the ALTER SCHEMA statement is as follows:

ALTER SCHEMA schema_name TRANSFER object_name

Example 12.6 shows the use of the ALTER SCHEMA statement.

USE AdventureWorks;
ALTER SCHEMA HumanResources TRANSFER Person.Contact;Code language: PHP (php)

Example 12.6 alters the schema called HumanResources of the AdventureWorks database by transferring into it the Contact table from the Person schema of the same database.

The ALTER SCHEMA statement can only be used to transfer objects between different schemas in the same database. (Single objects within a schema can be altered using the ALTER TABLE statement or the ALTER VIEW statement.)

DROP SCHEMA

The DROP SCHEMA statement removes a schema from the database. You can successfully execute the DROP SCHEMA statement for a schema only if the schema does not contain any objects. If the schema contains any objects, the DROP SCHEMA statement will be rejected by the system.

As previously stated, the system allows you to change the ownership of a schema by using the ALTER AUTHORIZATION statement. This statement modifies the ownership of an entity.