In SQL 2005, you might run into a situation where you need to create an ALTER TABLE script, such as when adding a new column to the database or changing the field type, etc.
If you want to script a specific SQL function like CREATE, SELECT, UPDATE, etc., you usually just right click on the table in SQL Management Studio and select Script Table as and then the function you want to script
However, the ALTER To option is disabled in the script table as a menu option! So if you want to create a script, how do you go about it? Well, you cannot do this when you change the table.
To generate a script, you first right-click the table you want to modify and select Design.
Now go ahead and add your new columns, change the field types, set the fields to accept NULLS or not, etc. When you’re done, you want to right-click any column or in white space and you will see the option now available “Create change script”.
Now save this file and you have an ALTER TABLE script! This is a bit confusing as all other functions can be scripted just by right clicking, but it’s easy once you learn it. Enjoy!