Rename All References to a Database Object
You can update references to renamed database objects more quickly and accurately by using database refactoring. If you rename a database object in a database project, you must also update all references to that object so that they refer to it by its new name. For example, you must update any stored procedures that reference a table column that you have renamed.
By using refactoring, you can update references throughout the database project without manually tracking which objects depend on each other and then updating references one at a time. Even if you use sp_depends to find the dependencies, it does not always retrieve that information accurately, and you still must update each reference.
You can use refactoring to automatically rename the following types of objects:
Tables
Columns in tables, views, and functions
Stored procedure parameters
You can also use refactoring to update data generation plans, scripts, and unit tests. For more information, see Considerations before Renaming Database Objects
By default, refactoring does not update objects whose definitions cause one or more warnings or errors. In addition, refactoring does not automatically update the name of the .sql file that contains the definition for an object that you have renamed. You can rename the file manually in Solution Explorer.
Additional Uses of Rename Refactoring
You can also use rename refactoring to perform the following common tasks:
In a case-insensitive environment, you can change the case of an object name and all its references to match the casing conventions for your organization. For example, your database project might contain a mix of references to "Myid" and "MyID", and you can use rename refactoring to change all references to "MyID".
Note
References that were already the correct case are also updated (to the same value). If the file that contains those references is under version control, that file is checked out as part of the refactoring operation.
You can rename two or more objects so that their names and references match. For example, you can merge two schema names, such as "Person" and "Person Name". You can also merge two names that differ only by case in a case-sensitive environment, such as "MyID" and "Myid".
You can add square brackets around a schema object name and all its references to follow best practices for object naming. For example, you can replace all references to "MyID" with "[MyID]".
Note
References that already were enclosed in square brackets are also updated (to the same value). If the file that contains those references is under version control, that file is checked out as part of the refactoring operation.
When you perform these tasks, an error message warns you that an object already exists with that name. You must click Yes to make the change. However, you can preview and exclude individual changes if, in the Rename dialog box, you left the Preview changes check box selected.
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 your version control system 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 Rename 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 refactoring log file helps to 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.
Considerations Before You Rename
Before you rename references to a database object, you should understand additional considerations if any of the following statements are true:
You want to rename a column in a view.
You want to rename an object in a database project that contains one or more errors that prevent it from building successfully.
You have an unsaved data generation plan open.
You have a data generation plan in your project that uses the data bound generator.
You have database unit tests that do not use fully qualified names and those tests reference objects in other schemas or databases.
You have database scripts in your project that do not use fully qualified names and those scripts reference objects in other schemas or databases.
For more information about the special considerations in these scenarios, see Considerations before Renaming Database Objects.
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. You can also see how refactoring is used as a part of your database development life cycle by completing the introductory walkthroughs. |
Walkthrough: Apply Database Refactoring Techniques Walkthrough: Creating an Isolated Database Development Environment Walkthrough: Establishing a Baseline for the Isolated Development Environment Walkthrough: Performing Iterative Database Development in an Isolated Development Environment |
Rename a Database Object: You can use refactoring to change all references to a database 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 References to a Server or Database
Move a Database Object to a Different Schema
Fully Qualify the Names of Database Objects
Expand Wildcard Characters in SELECT Statements