Export (0) Print
Expand All

Readme_AdventureWorksScripts Sample

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.

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.

Transact-SQL

The AdventureWorks scripts use the following features of the Database Engine.

Application area Features

Database Engine

Schema DDL

Database Engine

Synonyms

Before you run this sample, perform the following tasks:

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.

ms160959.note(en-us,SQL.100).gifImportant:
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.

  • 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.

  • 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.

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.

  • 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.

  • 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.

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

  • 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.

Community Additions

Show:
© 2014 Microsoft