How to: Generate Change Scripts
You have the option of saving a change script when you are using Table Designer. A change script is a file with changes that can be applied to the database at a later time by selecting the change script in Solution Explorer and choosing Run from the shortcut menu.
You might choose to save a change script if you are not ready to update the database. For example, maybe you have made changes to tables in Table Designer that conflict with changes made by other users, or perhaps you do not currently have security permissions to alter a database.
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.
To save a change script
From the Table Designer menu click Generate Change Script. This command is available whenever you have unsaved database changes.
In the Save Change Script dialog box, choose Yes.
Note
If you select the Automatically generate a change script on every save option, you are prompted to generate a change script each time you save modified tables.
The Save As dialog box displays the default file name of the saved change script. Either accept the default file name or enter a different name, and then choose Save.
Note
If you are working on a database project (which you create from the New Project dialog box), the change script will be saved along with the project in the Change Scripts folder of the Solution Explorer.
Each time you save a change script, a new text file is created in the Change Scripts folder in Solution Explorer.
The change script file lists any changed tables and how they were changed (using the DROP TABLE, ALTER TABLE, or CREATE TABLE SQL statements). The change script file also contains any error handling code that is required to clean up temporary tables or to rollback transactions that were part of unsuccessful changes (changes that could not be saved). In addition, the change script file contains code to make the script run successfully against the database and code (Print statements) that describe what the script is doing when it runs. For applicable scenarios, the change scripts may also include the permissions sets on the tables that are being modified.
Any error messages that occurred while the changes were saved are stored in a log file, with the same name as the script file, except with a .log extension. You can also view messages showing which tables were successfully and unsuccessfully saved in the Log Viewer.