A schema is an object that contains other objects. Before using it, you must create it or you can use an existing schema. There are two types of schemas you can use, those built-in and those you create. When Microsoft SQL Server is installed, it also creates a few schemas. One of the schemas is named sys. Another is called dbo.

The sys schema contains a list of some of the objects that exist in your database system. One of these objects is called databases (actually, it's a view). When you create a database, its name is entered in the databases list using the same name you gave it.

To access the schemas of a database, in the Object Explorer, expand the Databases node, expand the database that will hold or own the schema, and expand the Security node.

To visually create a schema, right-click Schemas and click New Schema...

Object Explorer - New Schema

This would open the Schema - New dialog box. In the Schema Name text box, enter a one-word name. Here is an example:


After providing a name, you can click OK.

The formula to programmatically create a schema is:

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]

<schema_name_clause> ::=
    | AUTHORIZATION owner_name
    | schema_name AUTHORIZATION owner_name

<schema_element> ::= 
        table_definition | view_definition | grant_statement |
        revoke_statement | deny_statement 

The most important part is:

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]

In this case, start with the CREATE SCHEMA expression and add a name to it. Here is an example:

1> CREATE SCHEMA PrivateListing;
2> GO

The other parts deal with issues we have not studied yet.

Accessing an Object From a Schema

Inside of a schema, two objects cannot have the same name, but an object in one schema can have the same name as an object in another schema. Based on this, if you are accessing an object within its schema, you can simply use its name, since that name would be unique. On the other hand, because of the implied possibility of dealing with objects with similar names in your server, when accessing an object outside of its schema, you must qualify it. To do this, you would type the name of the schema that contains the object you want to use, followed by the period operator, followed by the name of the object you want to use. From our illustration, to access the Something1 object that belongs to Schema1, you would type:


As mentioned already, when Microsoft SQL Server is installed, it creates a schema named dbo. This is probably the most common schema you will use. In fact, if you don't create a schema in a database, the dbo schema is the default and you can apply it to any object in your database.


Home Copyright © 2007-2011 FunctionX.com Home