|
|
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...
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> ::=
{
schema_name
| 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
1>
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:
Schema1.Something1
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.
