SQL Server 2008: Create an alter table script

There are times where you need to save a table as a script in order to use it later. This is great if you want to script how the table is created or perhaps script an insertion of an item. If you, however, want to script a change to the table such as the addition of a column then you will see the following nuisance:

 

The “ALTER To”-item in the menu is grayed out. This is because you have to make the changes prior to scripting. Follow these steps to create a new column and script the changes:

1. Go to the design mode for the table in question:

 

2. Add you new column:

 

3: Right click on the column and select “Generate Change Script”:

 

4: Copy the script and do whatever you want with is:

 

To test the script you close the design WITHOUT SAVING IT. You then open a new query window, select the appropriate database and run the query. This way you can guarantee that the query is correct:

Leave a Reply