08/05/2008 01:28:33
This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
In SQL Server, scripts are available in SQLServerEngineSamples.msi that provide two alternatives to using the schemas in the AdventureWorks sample database.
Scenario
In SQL Server, tables and other schema-scoped objects are contained in schemas, and the schemas are owned by users. In AdventureWorks, five schemas are used to contain schema-scoped objects that are based on business functionality. For example, customer and sales-related objects are contained in the Sales schema; employee-related objects are contained in the HumanResources schema, and so on. For more information, see "Schemas in AdventureWorks" in SQL Server Books Online.
The addition of schemas introduces changes to the way in which you access tables and other schema-scoped objects. To access objects in a schema other than the default schema (DB), at a minimum, a two-part identifier in the form schema_name.object_name must be specified. This is true for all DDL and DML statements that reference schema-scoped objects.
The scripts included in this sample provide alternatives to working with schema-scoped objects. One alternative transfers all schema-scoped objects to the dbo schema and the other creates synonyms for each schema-scoped object in the dbo schema.
Languages
Transact-SQL
Features
The AdventureWorks scripts use the following features of the Database Engine.
| Application area | Features |
|---|---|
|
Database Engine |
Schema DDL |
|
Database Engine |
Synonyms |
Prerequisites
Before you run this sample, perform the following tasks:
-
Install SQL Server and make sure you include the following components:
-
The Database Engine
-
SQL Server Management Studio
-
The Database Engine
-
Download the AdventureWorks (OLTP) sample database and Database Engine samples available at the
Microsoft SQL Server Samples and Community Projects Web site .
Transferring Objects to the dbo Schema
The AlterSchemaToDbo.sql script transfers every schema-scoped object in AdventureWorks to the dbo schema. After this script has been run, users who have a default schema of dbo will not have to use a two-part identifier when they reference these objects in DDL and DML statements.
| Important: |
|---|
| The code examples and samples provided with SQL Server will not run after the AlterSchemaToDbo.sql is executed unless the schema names specified in the code are replaced with dbo or removed. |
The AlterSchemaFromDbo.sql script transfers (returns) the objects from the dbo schema to the schemas they were in before running the AlterSchemaToDbo.sql script.
Running the Scripts to Transfer Objects to and from the dbo Schema
The following procedure shows how to run the Transact-SQL script that transfers AdventureWorks schema-scoped objects to the dbo schema.
To run the AlterSchemaToDbo script
-
In SQL Server Management Studio, open the file AlterSchemaToDbo.sql script located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click Execute.
The following procedure shows how to run the Transact-SQL script that returns AdventureWorks schema-scoped objects from the dbo schema to their original schemas.
To run the AlterSchemaFromDbo scripts
-
In SQL Server Management Studio, open the file AlterSchemaFromDbo.sql script located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click Execute.
Using Synonyms
A synonym is an alternative name given to a schema-scoped object. The synonym is specified instead of the base object in DDL and DML statements.
The CreateSynonymsDbo.sql script creates a synonym for each schema-scoped object in AdventureWorks. The synonym name is the same as the base object name, but uses the dbo schema. For example, the synonym for HumanResources.Department is dbo.Department.
Using synonyms has the following advantages:
-
If dbo is the default schema, a two-part identifier is not needed to specify these objects in DDL and DML statements.
-
The code examples and samples provided with SQL Server can be used without modification.
The DropSynonymsDbo.sql script drops the synonyms that are created by the CreateSynonymsDbo.sql script.
Running the Synonym Scripts
The following procedure shows how to run the Transact-SQL script that creates synonyms in the dbo schema for each AdventureWorks schema-scoped object.
To run the CreateSynonymsDbo script
-
In SQL Server Management Studio, open the file CreateSynonymsDbo.sql script located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click Execute.
The following procedure shows how to run the Transact-SQL script that removes the synonyms that are created by the CreateSynonymsDbo.sql script from the database.
To run the DropSynonymsDbo script
-
In SQL Server Management Studio, open the file DropSynonymsDbo.sql script located in the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, and then click Execute.
Removing the Scripts
Use the following procedure to remove one or more scripts that are included in this sample.
To remove the scripts
-
In Windows Explorer, locate the C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\AdventureWorks\Scripts folder, right-click the script name and click Delete.