This chapter is from the book
This chapter is from the book
This chapter is from the book
Change Scripts
Change scripts are used to apply changes to an existing database schema. Although these scripts could be written manually, it is preferable to use a tool to generate them. When we use the Visual Database tools that we used in Chapters 1 and 2, Visual Studio automatically maintains a script of any changes made to the database schema that haven't yet been applied to the database.
Let's say that you want to add a new field, StartDate, to the tblEmployee table to track the start date of each employee. Do the following.
Open the Server Explorer, expand the Tables node of the Novelty database, and right-click on tblEmployee.
Select the Design Table menu item from the context menu to open tblEmployee in the Table Designer.
Add a new column named StartDate, of type datetime, as shown in Figure 8.6.
Figure
8.6 Adding the StartDate column to the tblEmployee table
Because you will want to apply this change to all the databases already installed and deployed at different sites in the field, you need to create a change script for what you just did.
Select the Generate Change Script menu item from the main Diagram menu or click on the Generate Change Script button on the Table toolbar. Doing so displays the Save Change Script dialog box, with a preview of the script, as shown in Figure 8.7.
Figure
8.7 The Save Change Script dialog box, showing the script to add the
StartDate column to tblEmployee Click on the Yes button. The standard Save As dialog is displayed and defaults to the Change Scripts folder of the current database project.
Click on the Save button to save the script with the default name of tblEmployee.
Close the table designer where you modified tblEmployee. When prompted to save changes to the table, click on the No button. In the next section we show you how to apply the changes by running the SQL script that you just created.
Double-click on tblEmployee.sql in the Solution Explorer to view the saved script, as shown in Figure 8.8.
Figure
8.8 Viewing the tblEmployee.sql script.
)
Figure
8.6)
Figure
8.7)
Figure
8.8