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.

SSMS Maturity

I’ve used a lot of tools with SQL Server over the years. We had a variety of individual tools from Microsoft for SQL Server 4.2, including the isqlw query editor that I used for years. I wrestled with the Enterprise Manager MMC plugin and eventually moved away to use the Embarcadero suite to work with SQL Server before coming back to Microsoft’s SSMS in SQL Server 2005. I’ve had a love/hate relationship with that tool ever since, though the Redgate suite of extentions has certainly made life easier.

However Management Studio (SSMS) hasn’t really evolved in quite a few years. It seems that the enhancements and additions that have been made with each version have been minimal, sometimes barely working and rarely improved across versions. It’s been disappointing that relatively few resources have been expended on SSMS, despite the regular evolution of SQL Server every 2-3 years. And despite the fact that one of the big reasons SQL Server was touted over Oracle and other RDBMSs is that the tooling was better. 

I suspect some of the problems were the pressure to release the core parts of SQL Server first, and link SSMS to the server product, despite the fact that it really needs to support multiple versions and previous tooling should have been improved.

That’s changing a bit. As Tim Ford noted, SSMS now has it’s own release and upgrade path. The tool should not have it’s own, separate download and lightweight installer along with a separate release cadence from SQL Server. I suspect this will evolve more rapidly, probably closer to an Azure like schedule, with more regular patches and enhancements. At least that’s what I hope.

I was glad to see SSMS being made available for 2012 and 2014 versions as a separate download, and the change to a completely separate product that will likely become de-linked from SQL Server versions. I expect we’ll just have SSMS in the future, with some version that’s more like what we see in Chrome and Firefox. 

For now SSMS is still based on the 2010 Visual Studio shell, but the comments in this announcement seem to indicate it will move to the 2015 shell soon. Let’s hope that happens and the performance improves along with the maturity of the tool in the future.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and LibSyn.

See Two Queries at Once in SSMS

I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

One of the things that I’ve struggled with a bit in SSMS is sometimes comparing the results of two batches. I’m sure many of you have executed a query, then make a change, and execute it again, losing your results. Or you are testing something in two query windows and need to switch back and forth. Sometimes doing this, and only seeing one set of results (or checking if a query is finished) is cumbersome.

A few years ago I was watching Brent Ozar tune queries at an event and one of the things he wanted to do was compare two queries and their execution plans. He used vertical tab groups, which is a great way of seeing two things at once.

Here’s how my screen ended up during the comparison I was actually doing of three queries. I was checking credentials using a before, after, and with the DAC.


Things are a bit shrunk down as I wanted the image to be viewable. I actually had this full screen on a 30" wide monitor, and I could more easily see the queries and results from each window.

The easiest way to do this is to start with a query:


I want to change something, add a login, and test again, but I don’t want to lose my results. I’d also like to do an easy comparison. What I can do is go to the Window menu and get a new Vertical Tab Group. I could also do a Horizontal one, but comparing results is easier for me with vertical ones.


Once I select this, my selected window will move to a new tab group, and I’ll see two places where I can run code and visualize the queries and results at once.


I selected the left hand query, then clicked "New Query" to get a blank window. I then cut and pasted my code from the right to the left. This is exactly what you might want to do when tuning queries, keeping the original on the right while you work on the left.


Now I have two places to work on code. In my case, I wanted the before and after view of Server_principals as I checked some admin changes. I could do things and keep re-running the query in one of these windows, but keep the results from the other one visible.



Once again I was doing something else and realized the vertical window trick was handy. I killed the three windows I had, set up a new query, shot the screen, ran through the process with more screen shots.

Less than 10 minutes.


Watch Brent Tune Queries –

Custom SSMS

In my career, I’ve gotten used to working with SSMS on a variety of machines. It seems that as I toiled in various environments, I’d often connect to different SQL Server hosts, or use various consoles that only had the stock Enterprise Manager/SSMS environments installed. I do like larger fonts and a few other settings, so I learned how to quickly change those items, but otherwise I often have just learned to work with the default SSMS.

However that’s not a very efficient way to work. SSMS, and the Visual Studio shell on which it’s based, have a number of ways to customize and modify the application to suit the way you work. You can also even export and import settings to transfer them across machines.

Most software has a variety of settings, though I still don’t change many of them. As I’ve matured in my career, I do look to customize my environments to suit me, but somewhat lightly. There aren’t a lot of changes that I make, but I suspect that’s because of the nature of my work. I tend to do what works, and I don’t often explore other settings. This week I’m wondering if you have tips and tricks.

What customizations or add-ins do you use to customize SSMS (or other development software)?

I’m curious what many of you do and how you do it. I don’t often explore the possibilities as I’m usually concerned with just getting work done. However I’m sure that I’m missing opportunities to work smarter. I’ve never been a big fan of the function keys, though I know some people love them. I’ve tended to rely on templates (and snippets in SQL Prompt) to work faster.

If you’ve got idea, tweaks, or software, let us know. I’m sure that many of us would appreciate suggestions that might help ease our jobs.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.0MB) podcast or subscribe to the feed at iTunes and LibSyn.

Quick Tips–SSMS Select a vertical block

I saw this years ago in a presentation from Aaron Bertrand. At the time I thought it was super cool and I’d use it all the time, but I haven’t found many uses. However since I needed to do this recently, this helped.

Imagine that you have this:


A normal select statement. Perhaps you’ve qualified columns with SQL Prompt, or you’ve used some tool to enter this (or you’re a typing masochist). Now you add an alias for the column because you don’t want to type the full name everywhere. That causes SSMS to complain.


You can’t run this because once you use an alias, you need to use it elsewhere. The full table name isn’t valid anymore.

Now you could do a search and replace (CTRL+H), but that presents other problems, not the least of which is replacing the table in the FROM name. Unless you want to go through and approve or deny every replacement. You could also edit Person to “p” on each line manually.

Hey, this is programming, we don’t do things over and over when we can avoid them.

Enter Block Select

If you place your cursor here, shown with the arrow as my capture tool missed it.


Now I can click ALT+Shift and hold them down while I move my cursor to the lower right of the block I want to select. In this case, it’s between the “n” and period on the last line of the column list, above the FROM clause. Look at the image below.


I’ve now selected a block, and I can hit delete. This gives me:


Notice that my selection is a thin cursor still visible. I can actually type here. Imagine I typed “sn” now. This is what I’d get.


I fixed the alias before I shot this, and once I moved the cursor, I lost my selection, but a simple ALT+Shift, lets me highlight, select, and type in a vertical block.

Handy when trying to correct a number of items on separate lines.

Quick Tips–SQL Prompt Qualifying Columns

I love SQL Prompt, and think it’s a great productivity tool. Even before I worked at Red Gate, I love the tool and had a copy before Red Gate bought the technology from the original developer. Recently I’ve run into a few people that weren’t aware of some of the ways in which it can help you. This is a quick look at one of the ways I use SQL Prompt.

Qualifying Columns

One of the things that’s a good programming practice for T-SQL is to qualify your columns. Imagine that I have this query:


Note that my column names are listed with just the column name and don’t include the table from which they come. Not a big deal here, but as I enhance this code over time, I may add another table to a join, perhaps one that includes BusinessEntityID in it. In that case, I’ll get an ambiguous column error, and a squiggly in SSMS (shown below).


SQL Prompt tries to make writing code quicker and easier, and if I look back to my first query, Prompt can qualify those columns for me.

If I press CTRL+B, CTRL+Q, I’ll get this (from the first query).


Note that every column now includes the table names.

It also works for aliases. If I have this (note I’ve added an alias)


CTRL+B, CTRL+Q gives me this:


As I add tables and modify this code, anytime I find columns unqualified, I can use this quick shortcut to fix my code.

Note: If you have ambiguous columns, Prompt can’t fix them (yet).

Customizing SSMS–Results

This is a short series on some customizations in SSMS to make it visually more appealing.

As a presenter, I’ve learned how to change the way Management Studio (SSMS) looks to make it easier for people to see the screen on a projector. I had wrongly assumed that so many people knew many of these tricks, which is a poor attitude on my part. That should be especially apparent as I saw a presentation recently where the speaker didn’t know how to make things easier to see.

In a previous post I talked about changing the query font size. In this post we’ll look at result sizes.

The Defaults

This is what I see, and it’s not easy to read.


The results are much smaller than the query itself. How can we change this and make things easier to read?

I wrote about one way: using results to text. This works well, but it isn’t the best solution for everyone, and you still want to change the size of your results. The technique I’ll show you works for that as well.


If you go to the Tools | Options menu, in the Fonts tab as I showed in the last post, you’ll find there’s a drop down titled “Show settings for:” at the top. If you select Grid results, you can change the font settings.


The default is 8, and I usually up that a bit.


Once that is done, you click OK, and get this note:


Make sure your tabs are saved, since you need to restart SSMS. Once you do, your results will be much easier to read.


Note that in the drop down for settings, there is an item for “Results to text”. If you change that, your text results will be larger.

Customizing SSMS – Bigger Queries

This is a short series on some customizations in SSMS to make it visually more appealing.

As a presenter, I’ve learned how to change the way Management Studio (SSMS) looks to make it easier for people to see the screen on a projector. I had wrongly assumed that so many people knew many of these tricks, which is a poor attitude on my part. That should be especially apparent as I saw a presentation recently where the speaker didn’t know how to make things easier to see.

Here’s another item I saw someone struggle with recently, the query font size.

The Default View

When you install SSMS, this is what you often get, and how your queries look:


Not easy to read, especially for these old eyes. When someone uses the default settings in SSMS and presents on a big screen, I am usually struggling to see, sometimes even when I sit in the front row.


You can make SSMS easier to read for yourself in a couple ways. The first is the quickest, but it’s a change for only the current SSMS execution. If you close and re-launch it, things will return back to the previous settings.

If you look below the query and above the results, there’s a drop down that says 100% in it.


If you click that, you’ll get a drop down of some percentages you can choose.


These percentages will change the size of the query pane. For example, if I choose 200%, you can see things are much easier to read. The query pane is much better than the results. This is great for quick changes when you are presenting.


For permanent changes, this is what I quickly do. Go to the Tools menu at the top of SSMS and select Options. You will get this dialog:


I’ve selected “Fonts and Colors” from the left already, but once you do that, there’s a “Size” drop down on the right. Choose that.


I usually set that to 14 and it looks good for me. A balance of large size, but still being able to get lots of code on the screen.


This make it easier to read your code. In another post I’ll talk about results.

Customizing SSMS – Hiding the panels

This is a short series on some customizations in SSMS to make it visually more appealing.

As a presenter, I’ve learned how to change the way Management Studio (SSMS) looks to make it easier for people to see the screen on a projector. I had wrongly assumed that so many people knew many of these tricks, which is a poor attitude on my part. That should be especially apparent as I saw a presentation recently where the speaker didn’t know how to make things easier to see.

Note that I often caution writers not to assume too much from the reader, and here I took it one step further, assuming some topics weren’t worth writing about.

Here’s one that I heard asked recently: how do you hide the side panels in SSMS?

The Default View

Here’s what SSMS looks like for most people.


The Object Explorer takes up a good portion of the screen on the left. For some people, they may have this:


Or even this:


In the last two images, the Solution Explorer and Properties panes are on the right. If you have these panes open, you might find yourself squeezing a query into a small space in the middle. If your resolution is 1024×768 (standard for many projectors), you might get very little on the screen.


There’s a way to clean things up quickly and easily. All of these panes have this item in their upper right sides:


If you click the middle, pin icon, the “Auto Hide” will be enabled, and the panes will slide to the side. They’ll still be there, but now they’ll be in a side bar, like the Object Explorer is below:


If I hover the mouse over the “Object Explorer” button on the side, it will appear, or I can click it.

I can always use the “View” menu at the top as well (or a shortcut) to expose any of these panes. However when I’m presenting, or want to make a quick change, the “auto hide” feature works very well for me.