Readme_AdventureWorksScripts Sample

11/05/2008 21:36:06


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 schemaname.objectname 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:

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
To run the AlterSchemaFromDbo scripts

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.
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
To run the DropSynonymsDbo script

Removing the Scripts

Use the following procedure to remove one or more scripts that are included in this sample.
To remove the scripts