Readme_AdventureWorksScripts

  • Article

In Microsoft SQL Server 2005, scripts are available in SQLServerEngineSamples.msi that provide two alternatives to using the schemas in the AdventureWorks sample database.

Scenario

In SQL Server 2005, 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 SQL Server 2005 Database Engine.

Application area Features

Database Engine

Schema DDL

Database Engine

Synonyms

Prerequisites

Before you run either of the scripts included in this sample, install SQL Server 2005 and make sure you include the following components:

  • Database Engine
  • SQL Server Management Studio
  • The AdventureWorks database which is included with SQL Server 2005, and is also available at the SQL Server Developer Web site.
  • The SQL Server 2005 Database Engine samples. These samples are included with SQL Server 2005. You can download the latest version of the samples at the SQL Server Developer 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 2005 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\90\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\90\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 2005 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\90\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\90\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\90\Samples\Engine\Administration\AdventureWorks\Scripts folder, right-click the script name and click Delete.