In the typical dev environment, there is a development
server, a functional testing server, a quality assurance server, and one or
more production servers. Let’s suppose that your mandate is to modify a table
on the development server, adding a column or changing a column’s name or data
type, and yet you still have 50 or so more changes to make. Now it’s time to
roll out the changes to the other servers in cascading order.

SQL Server 2005 Management Studio comes to the rescue with
its handy Generate Change Script button, which you can see in Figure A. The Generate Change Script
button appears after you right-click a table and choose Modify from the
shortcut menu. The code attached to the button analyzes the current table
definition, compares it to your changed definition, and writes a script that
will alter the old table to conform to the new definition. By running this
script on a remote database, you can make changes to databases installed at
client or branch sites.

Figure A

Figure A: Generate Change Script button

Now you’re asked to change all nchar
and nvarchar columns to char and varchar
respectively. This could be a minefield, particularly where foreign keys are
concerned. (For the moment, we will sidestep the issue of using such columns as
primary keys, as opposed to identity columns.)

Weekly SQL tips in your inbox

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!

When you begin to make these changes, you’ll discover
another cool hidden feature in SQL Server 2005 Management Studio: the Data Type
Change. The dialog box will appear if you change the data type or anything else
on a column that is a foreign key. For example, after changing all instances of
nchar to char and nvarchar
to varchar, the Data Type Change Required dialog box
(see Figure B) popped up. After reading
the warning that I might lose data due to the conversion, I clicked the Yes
button to proceed.

Figure B

Figure B: SQL Server 2005 Management Studio detects that the CustomerID column is a foreign key elsewhere, and notifies you that all instances will be changed.

SQL Server 2005 Management Studio generated the code to
perform all these changes and presented me with a preview for my verification. (Note:
In the preview window, you can’t see all the code, but you can select it all
and paste it into an editor for easier viewing.)
Listing C contains the generated code.

If you are faced with this type of change request and it
reverberates through your entire database, then generating individual scripts
like the one presented above can be quite tedious. You can save time by using
various solutions, such as data-modeling tools or Red Gate
Software’s SQL Compare, which can “diff” two databases and update
one from the changes made to the other. But, when your tasks are less
monumental, SQL Server 2005 Management Studio does a wonderful job.

Miss a column?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller’s column.