In Microsoft SQL Server 2005, scripts are available in SQLServerEngineSamples.msi that provide two alternatives to using the schemas in the AdventureWorks sample database.
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.
Before you run either of the scripts included in this sample, install SQL Server 2005 and make sure you include the following components:
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.
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.
|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.
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.
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.
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.
Use the following procedure to remove one or more scripts that are included in this sample.