Export (0) Print
Expand All

Considerations before Renaming Database Objects

Before you rename a database object in Visual Studio Premium, you should consider the following issues:

Renaming Columns in Views

Effects of Build Errors

Effects on Data Generation Plans

Effects on Unit Tests

Effects on Transact-SQL Scripts

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
NoteNote

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.

When you rename a database object in Visual Studio Premium, 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.

When you rename a database object in Visual Studio Premium or Visual Studio Ultimate 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, all open data generation plans are saved and closed automatically, and the rename operation continues.

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

For more information, see Generating Test Data for Databases by Using Data Generators.

Transact-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:

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

  • Transact-SQL statements in a unit test can refer to objects in the same database, but in different schemas.

When you rename a database object in Visual Studio Premium, 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 Verifying Database Code by Using Unit Tests.

Transact-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:

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

  • Transact-SQL statements in a script can refer to objects in the same database, but in different schemas.

When you rename a database object in Visual Studio Premium, 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 Creating and Modify Database Scripts.

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.

Community Additions

ADD
Show:
© 2014 Microsoft