Creating and managing database schemas

A schema is a logical container that groups objects of similar scope or ownership together. By default, the database owner (dbo) schema is automatically created in a SQL Server database. Unless you specify otherwise, all SQL Server user objects are created in the database owner (dbo) schema. You can define different default schemas for each user of the database. When you create a user database, SQL Server automatically creates these schemas: sys, dbo, INFORMATION_SCHEMA, and guest.

You can query the sys.schemas system catalog view to see the schemas defined for the database. The following is the syntax to query this system catalog:

SELECT * FROM sys.schemas

You can also use SQL Server Management Studio 2014 to list the schemas defined for the database. To list the schemas defined for the database in SQL Server 2014 Management Studio, use the following steps:

  1. In Object Explorer, expand the Databases folder.
  2. Next, select the database and expand the Security folder.
  3. Finally, expand the Schemas folder to list the schemas defined for the database.

Managing schemas using T-SQL DDL statements

You can use the CREATE SCHEMA statement to create a schema. The following is the basic syntax of this command:

CREATE SCHEMA schema_name
AUTHORIZATION user_or_role

We use the ALTER SCHEMA statement to transfer objects from one schema to another. The following is the basic syntax of this command:

ALTER SCHEMA schema_name
  TRANSFER [ <entity_type> :: ] securable_name [;]

To delete a schema, we use the DROP SCHEMA statement as follows:

DROP SCHEMA schema_name

Managing schemas using SSMS 2014

The following are the steps to create a schema from SQL Server 2014 Management Studio:

  1. In the Object Explorer window, expand the Databases folder.
  2. Next, expand the database in which you want to create a schema.
  3. Expand the Security folder and then right-click on the Schemas folder.
  4. Choose New Schema and then enter the schema name and owner. You can view and select from a list of available owners by clicking on the Search button in the New Schema dialog box.
  5. Click on OK to create a schema.

To delete a schema, right-click on the schema and choose Delete.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.138.204.186