Walkthrough: Renaming a Database Column
You can use database refactoring to rename a schema object. Other objects that reference the renamed object are automatically updated with the new name. For example, you can rename a column in a table, and any stored procedures that reference that column are automatically updated with the new name. For more information, see Overview of Rename Refactoring.
Note
In queries that select objects from more than one table, refactoring is supported only when column names are fully qualified using the table name or a table alias. This restriction applies even for column names that are not ambiguous. For example, the following statements can be correctly updated when you rename a column to which they refer:
SELECT Orders.OrderID, Customers.ContactName FROM Orders JOIN Customers on Orders.CustomerID = Customers.CustomerID
or:
SELECT o.OrderID, c.ContactName FROM Orders o JOIN Customers c on o.CustomerID = c.CustomerID
In this walkthrough, you create a database project, import the Northwind database schema, examine existing schema objects, and rename a schema object.
Prerequisites
To complete this walkthrough, you need the following:
Visual Studio Team System Database Edition.
Microsoft SQL Server 2000 or SQL Server 2005.
Access to a database server that has the Northwind database installed.
Creating a Database Project
First, you create a new database project to use for the walkthrough. In a team environment, you typically check an existing project out of your source code control system to work on a database project. For more information, see Refactoring Database Objects in a Team Environment.
To create a database project
On the File menu, point to New, and then click Project.
The New Project dialog box appears.
In the Project types list, expand the Database Projects node, and click Microsoft SQL Server.
In the Templates list, click SQL Server 2000.
In Name, type RefactorNorthwind, and click OK.
A solution is created that contains the RefactorNorthwind empty database project. This project is your test (or sandbox) project. No one else has access to your sandbox project while you work on it.
Importing the Northwind Database
Next you import the database schema from the Northwind database. You use this copy of the Northwind schema to test refactoring.
To import the Northwind database
In either Solution Explorer or Schema View, click RefactorNorthwind.
On the Data menu, click Import Database Schema.
Note
You can also right-click RefactorNorthwind and then click Import Database Schema in either Solution Explorer or Schema View.
The Import Database wizard appears.
In the Source database connection list, click the connection that corresponds to your existing Northwind database.
.gif "Important note")
Important Note:If you have not yet connected to that database, you must first click New Connection to create a connection to it. For more information, see How to: Create a Database Connection.
Click Finish.
As the schema is imported, project items that correspond to the objects in your database appear under the database project in Solution Explorer and Schema View.
Note
Even though you connected to the database to import the schema, you are now disconnected and working offline.
Examining Existing Database Objects
Next you examine existing objects in the Northwind database before you use refactoring.
To examining existing database objects
In Schema View, expand the Tables subfolder, expand the dbo.Orders table, and expand the Columns folder.
The columns appear. The Orders table includes a column named ShippedDate.
Expand the StoredProcedures subfolder, and double-click dbo.CustOrdersOrders.
A script file opens, and the stored procedure appears. The stored procedure accepts a customer ID and returns a list of the orders placed by that customer. The stored procedure selects the ShippedDate column from the Orders table.
Renaming a Database Object
Next you use refactoring to rename a database object. You rename a column in the Orders table and examine the results in the preview dialog box.
To rename a database object
In Schema View, expand the RefactorNorthwind project, expand the Tables subfolder, expand the dbo.Orders table, and expand the Columns folder.
The columns appear.
Click the ShippedDate column.
On the Data menu, point to Refactor, and then click Rename.
The Rename dialog box appears.
Note
You can also right-click ShippedDate in Schema View, point to Refactor, and then click Rename.
In New name, type ShippedDateAndTime.
Select the Preview changes check box, and click OK.
The Preview Changes dialog box appears and contains an upper pane and a lower pane. The upper pane contains a tree that lists the objects that will be changed, and the lower pane shows the script that will be generated to make the changes. The upper pane displays the old name, and the lower pane displays the new name.
In the upper pane, expand Schema Objects, expand Orders.table.sql, and then click Orders.table.sql.
The lower pane displays a create table script with the new column name, ShippedDateAndTime, highlighted.
In the upper pane, expand Schema Objects, expand CustOrdersOrders.proc.sql, and then click CustOrdersOrders.proc.sql.
The lower pane displays a create procedure script with the new column name, ShippedDateAndTime, highlighted.
Click Apply.
The changes are made. The column name is updated, and the new column name appears in Schema View for each object that was updated.
Next Steps
In this walkthrough, you created a database project, imported the Northwind database schema, and renamed a database object. At this point, the changes that you have made are only to the database project, not to the database itself. Next you can deploy your changes to the database and verify the results. For more information, see Walkthrough: Deploying Database Refactoring Changes.
See Also
Tasks
How to: Rename Database Objects
Concepts
Terminology Overview of Database Edition