Move a Database Object to a Different Schema
When you move a database object to a different schema, you can use database refactoring to more easily and accurately update all references to that object in your database project. For example, you might decide to split your database into multiple schemas, perhaps to increase security or to organize it more logically. After such a split, you must not only move one or more objects to the new schema but also update every fully qualified reference to that object. You might make errors when you manually change all references to refer to the new schema. By using database refactoring, you can find and update those references automatically.
Preserving Your Intent With The Refactoring Log
When you rename a database object, an entry is added to the refactoring log for your database project. When you deploy your changes, this log helps ensure that the corresponding object in the target environment is renamed as you intended. Otherwise, the existing object would be dropped, and an object would be added with the new name. The log is maintained in an XML file that is named ProjectName.refactorlog. You check this file in to version control when you check in the other files that compose your database project. This log file differs from the optional files that are created when you select the Generate Refactoring Log check box in the Move to Schema dialog box. The optional files provide a record of all refactoring operations that you performed with that check box selected. The ProjectName.refactorlog file contains only information about refactoring operations that require special handling during deployment.
Deployment
When you use refactoring, you update only your database project, instead of a production database. By following this strategy, you gain all the benefits of database projects, which include version control and team development. When you build the database project, all refactoring log files are merged into the transaction.refactorlog file in the \sql folder of your project. When you deploy your changes, that merged factoring log file helps preserve the intent of your changes to the database project.
For more information, see Build and Deploy Databases to an Isolated Development Environment.
Note
In a team environment, you should run application and database unit tests before you deploy your changes to a production server. For more information, see Starting Team Database Development.
Common Tasks
In the following table, you can find descriptions of common tasks that support this scenario and links to more information about how you can successfully complete those tasks.
Task |
Supporting Topics |
|---|---|
Get hands-on experience: You can become familiar with how to move database objects to a different schema, in addition to other types of refactoring, by following the walkthrough. |
|
Move a database object to a different schema: You can use refactoring to move a database object to another schema and automatically update all references to that object in your database project. As part of the refactoring operation, you can preview the changes before you apply them. |
|
Undo a refactoring operation: If you decide that a refactoring operation needs to be reversed, you can undo that refactoring operation in the current session of Visual Studio. |
|
Deploy database refactoring changes: After you refactor the database project, you must deploy those changes to a target database. Typically you will deploy your changes to your isolated development environment to test them before you check them in to version control. |
|
Troubleshoot problems: You can learn more about how to troubleshoot common problems with database refactoring. |
Related Scenarios
Rename All References to a Database Object
Rename References to a Server or Database
Fully Qualify the Names of Database Objects
Expand Wildcard Characters in SELECT Statements