Getting Table Change Scripts–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the really basic things I think everyone should understand is how to get scripts from Management Studio (SSMS) and saving them. In fact, I’ve written that everyone should use this button and really not ever execute their GUI changes. Capture the script, save that, and automate things.

However, that’s not what this post is about. This post is about how you get a script to look at changes, or better understand how SSMS might implement your changes.

Editing a Table

Let’s say that you want to redesign a table, so you Edit it in the SSMS Table Designer. Here, you can see I have small table with a few fields.

2016-06-27 09_33_55-PLATO_SQL2016.EncryptionDemo - dbo.OrderDetail - Microsoft SQL Server Management

I want to rename the field with incorrect casing as well as insert an OrderDate column in the middle. I have made those changes below.

2016-06-27 09_34_31-PLATO_SQL2016.EncryptionDemo - dbo.OrderDetail_ - Microsoft SQL Server Managemen

Now, I’m not sure how these changes will be made in SSMS, and I certainly want to be careful in production. We want a script we can examine and approve.

Certainly, I could use something like SQL Compare to generate a script between two databases. That would include transactions and error handling and more. That’s my preferred method. However, since not everyone has SQL Compare (a mistake! Winking smile ), let’s just use SSMS.

Instead of saving, I’ll click this button.

2016-06-27 09_37_09-PLATO_SQL2016.EncryptionDemo - dbo.OrderDetail_ - Microsoft SQL Server Managemen

Or I’ll go to this menu item.

2016-06-27 09_37_52-PLATO_SQL2016.EncryptionDemo - dbo.OrderDetail_ - Microsoft SQL Server Managemen

Once I do that, after a warning, I get a script dialog.

2016-06-27 09_39_28-PLATO_SQL2016.EncryptionDemo - dbo.OrderDetail_ - Microsoft SQL Server Managemen

I can now save the script and then open it back  up in SSMS. I can see all the changes that the scripting engine thinks we should make.

2016-06-27 09_41_29-OrderDetail.sql - (local)_SQL2016.master (PLATO_Steve (57)) - Microsoft SQL Serv

This allows me to learn about one way to make these changes, as well as see things that might concern me, such as poorly named constraints and indexes.


This is a great productivity and learning technique, but also a core thing I’d hope most DBAs knew. You could certainly write about how you use this, or how this might have been helpful in a situation. Showcase your knowledge on this topic with the #SQLNewBlogger hashtag.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.