Considerations before Renaming Database Objects

Before you rename a database object in Microsoft Visual Studio Team Edition for Database Professionals, you should consider the following issues:

Preventing Data Loss when Renaming Columns and Tables

Renaming Columns in Views

Effects of Build Errors

Effects on Data Generation Plans

Effects on Unit Tests

Effects on T-SQL Scripts

Preventing Data Loss when Renaming Columns and Tables

When you rename a database object, the deployment script that is generated when you build the project reflects that change. When you rename a column or a table, the script drops the original object that has the original name and adds the new column or table with the new name. If you deploy this change to the existing database as an update, the data in the original column or table will be lost.

You can control whether the original column or table is dropped by setting the appropriate option in the project properties. You can do this by opening the project properties, clicking the Build tab, and selecting or clearing the Generate DROP statements for objects that are in the target database but that are not in the database project check box. If you select this check box, the drop statement for the old object is included in the deployment script that is generated when you build the project. If you clear this check box, the drop statement for the old object is not included in the deployment script that is generated when you build the project.

To prevent the potential data loss caused by the drop statement, you must plan ahead. The following examples show approaches you can use to migrate your data safely:

  • Replace the drop/add statements with sp_rename statements.

  • Do not deploy the drop statement. You will have copies of both the old object and the new object, and you can move the data between them. You can move the data manually by using a select into or other T-SQL statement. You can use Bulk Copy Program, Data Transformation Services (SQL 2000), or SQL Server Integration Services (SQL 2005) to move the data automatically.

  • Before you deploy the change, move the data from the old table to a temporary location (for example, to a table on a different database). Deploy the drop and the add statements. Move the data from the temporary location to the new object. You can move the data manually by using a select into or other T-SQL statement. You can use Bulk Copy Program, Data Transformation Services (SQL 2000), or SQL Server Integration Services (SQL 2005) to move the data automatically.

For more information, see Protecting Data during a Renaming Operation.

Renaming Columns in Views

A view consists of a statement that selects columns from tables or other views. The tables that are used in the view are known as the base tables or underlying tables. For example, the following code creates a view that is based on the HumanResources.Employee table:

CREATE VIEW dbo.vEmployeeTest
AS 
     SELECT EmployeeID, Title
       FROM HumanResources.Employee

When you rename a column in a view, the column is not renamed in the underlying table. Instead, the name in the view is aliased, as in the following examples:

CREATE VIEW dbo.vEmployeeTest
AS 
     SELECT EmployeeID, Title AS JobTitle
       FROM HumanResources.Employee

CREATE VIEW dbo.vEmployeeTest (EmployeeID, JobTitle)
AS 
     SELECT EmployeeID, Title
       FROM HumanResources.Employee

Note

If a view uses SELECT * to get the data from the underlying table, the * is expanded to list the individual columns. The renamed column is aliased as in the previous examples.

If you want to rename the column in both the view and the underlying table, rename the column in the table instead, which automatically updates the column in the view.

Effects of Build Errors

When you rename a database object in Team Edition for Database Professionals, an attempt is made to update other database objects, data generation plans, unit tests, and scripts that refer to that object. While you work in a database project, you might perform an action that introduces a build error, and then perform another action that fixes that build error. For example, you might delete a table, and then you might update a view that depends on that table to remove the reference to the deleted table. In between deleting the table and updating the view, your project will have build errors.

If you rename a database object when you have build errors in your project, the object can still be renamed correctly. However, it might not be possible to correctly update all references to the object that you are renaming. If there are build errors in your project you are warned on the Preview Changes dialog box. If you continue, the object is renamed and references are updated as much as possible. If you cancel the operation, you can fix the build errors first and then try the rename operation again.

The Preview Changes dialog box displays warnings for the following:

  • Build errors in the database project

  • Syntax errors in scripts and unit tests

  • Malformed data generation plans

Effects on Data Generation Plans

When you rename a database object in Team Edition for Database Professionals, an attempt is made to update data generation plans that refer to that object. However, you should consider the following:

  • Before you can rename an object, you must save any data generation plans that are open in the editor. If you have data generation plans open when you try to rename an object, you will be prompted first to save the plans. If you continue, all open data generation plans are saved and closed automatically, and the rename operation continues. If you do not continue, the rename operation is canceled. All open data generation plans are left open, and are not saved.

  • You must manually update data generation plans that use data bound data generators..

For more information, see Generating Data with Data Generators.

Effects on Unit Tests

Transact-SQL (T-SQL) statements in a unit test usually refer to the objects in the database specified in the ValidationConnectionString and ExecutionConnectionString of the unit test. The following are two possibilities where this is not the case:

  • T-SQL statements in a unit test can refer to objects in other databases.

  • T-SQL statements in a unit test can refer to objects in the same database, but in different schemas (SQL Server 2005).

When you rename a database object in Team Edition for Database Professionals, an attempt is made to update the unit tests in the solution that refer to that object. In the cases mentioned earlier, it might not be possible to update the unit tests. To update unit tests when you rename a database object, you must make sure that you use the fully qualified name of objects in your unit tests. If you use fully qualified names in your unit tests, the refactoring engine can update them whenever you rename a schema object.

For more information about unit tests, see Overview of Database Unit Testing.

Effects on T-SQL Scripts

T-SQL scripts in a database project usually refer to the schema objects in the database project's database. The following are two possibilities where this is not the case:

  • T-SQL statements in a script can refer to objects in other databases.

  • T-SQL statements in a script can refer to objects in the same database, but in different schemas (SQL Server 2005).

When you rename a database object in Team Edition for Database Professionals, an attempt is made to update the scripts in the solution that refer to that object. In the cases mentioned earlier, it may not be possible to update the scripts. To update scripts when you rename a database object, you must make sure that you use the fully qualified name of objects in your scripts. If you use fully qualified names in your scripts, the refactoring engine can update them whenever you rename a schema object.

For more information about scripts, see Working with Database Scripts.

Security

If an error prevents a refactoring operation from completing, information about the error is written to the application event log, where any user who has "Normal User" permissions can view it. If your schema information is considered sensitive and can appear in the log, you might want to clear the log or restrict access to the client computer.

See Also

Concepts

Overview of Rename Refactoring
Terminology Overview of Team Edition for Database Professionals

Other Resources

Renaming Database Objects