Category: SQL Server Management Studio

Bert Wagner points out a useful feature of SSMS:

While all of these error messages point me in the right direction, I’m too lazyto try and remember how each version of SSMS refers to the error location. This becomes particularly problematic when jumping around environments and not being able to use a consistent version of SSMS.

There is one feature that works consistently though that makes navigating to the specific error lines easily.

Click through for that answer.  It doesn’t work for dynamic SQL, but it can be quite helpful when it does work.

Comments closed

Wayne Sheffield has another batch of SSMS tips and tricks for us.  First, he provides some helpful hints with comments.  Then comes a useful addition to SSMS 2016, comparing query plans:

Notice that various options have a colored non-equals icon. Here you can quickly see the various values that are different between the two execution plans.

At the bottom of the execution plans is a Showplan Analysis window. This window has color-coded keys for various sections of the plan:

He also shows how to import and export your SSMS configuration settings.  This makes it easier to migrate to a different machine or keep your desktop and laptop looking the same.

Comments closed

Wayne Sheffield continues his SSMS tools and tips series.  Since our last look, he’s added a few more tips.  First, Wayne shows how to show and hide blocks of T-SQL using Outlining.  Then, he gets to something I find useful in SSMS:

There are many editing items in SSMS that makes formatting and navigating your code easier than ever. Most of these Quick Editing Tips that follow are available from the Advanced submenu on the Edit menu

In particular, I like showing whitespace characters, as I’m kind of a whitespace tyrant.  But there are several other helpful options in that menu.

From there, Wayne shows how to use bookmarks in SSMS, which is something I tend not to do.  Finally, you can see the SSMS web browser.

Comments closed

Wayne Sheffield’s been busy since our last visit.  Here are six more SSMS tips and tricks.

First, Wayne shows how to create keyboard shortcuts for common activities.  Then, he shows how to color-code SQL Server instances, which is very helpful when trying to avoid accidental deployments to prod.

Next up he shows off the template explorer:

To use any of the templates, just double-click them. This template will be opened up in a new query window, ready for you to change.

You can also change the templates themselves. Just right-click on the template, and select “Edit”. The template will be opened up, and changes that you make will be saved to the template definition itself.

Finally, you can create your own templates. Right-click the root folder (SQL Server Templates), and you can select to create a new folder or template.

Templates are quite helpful when you commonly run the same bits of code.  Speaking of the same bits of code, Wayne next shows how to use Snippets.

His latest two posts are about the Object Explorer.  The first post shows you how to filter objects in the Object Explorer, and the second shows which columns you can include in it.

Comments closed

Wayne Sheffield continues his SSMS shortcuts series.  He starts off with a powerful way of selecting vertical columns of text.  Then he shows how to make text all lowercase or uppercase.

From there, he gets to one of my favorite features which I commonly forget exists:

We’re all used to using the clipboard in Windows programs. You copy something into it with Ctrl+C, and paste it into your document with Ctrl+V. However, did you know that the SSMS clipboard remembers the last 20 items that were put into the clipboard, and that you can cycle through all of these clipboard values? The keyboard shortcut Ctrl+Shift+V will paste the most recent item added to the clipboard. Using this shortcut repeatedly will cycle through the “Clipboard Ring”, pasting that item into the document. Now you don’t have to go back and copy items again!

Next, he shows how you can drag and drop to get all columns into a query window quickly.  Finally, Wayne shows you how to create shortcuts for important queries.  In my case, various forms of sp_whoisactive dominate this:  Ctrl+F1 for my desired layout, Ctrl-3 for my queries (three for me), Ctrl-4 for my desired layout plus execution plans (four for more).

Comments closed

Wayne Sheffield continues his SSMS tips & tricks series.  He first covers SQLCMD:

Notice that all of the SQLCMD commands start with a colon (:)

The complete reference for how all of these commands work is at http://msdn.microsoft.com/en-us/library/ms162773.aspx. I will cover just a few of them in this blog post.

SETVAR allows you to set a variable for use throughout the script. Its syntax is SETVAR . For instance, “:SETVAR SourceServer .\SQL2005” will set the variable SourceServer to “.\SQL2005”, which is the name of the SQL 2005 instance on my laptop.

Then he talks about multi-server queries, including local server groups and using a Central Management Server.  Having a CMS is critical when you have more than a couple of instances, and frankly, even then it can be helpful when bringing new people up to speed.

Wayne also covers the basics of regular expressions in SSMS.

All three of these are powerful tips.

Comments closed

Wayne Sheffield continues his series on SSMS tips and has four new posts for us.

Check out his posts on pinned tabs, easily opening or copying query file paths, template variables, and map mode:

Have you ever had a long script that you are trying to scroll through? Do you wish that you could see a preview of the scroll area to easily see what section you are in? Well, starting in SSMS 2016, you can. Just right-click the vertical scroll bar, and select “Scroll Bar Options…”

Of the four, map mode is my favorite.  It’s extremely helpful when going through large files.

Comments closed

Wayne Sheffield has pledged to do a month worth of SSMS tips and he’s off to a good start.  Here’s day 1, where he talks about creating a solution in SSMS:

Are you the type of person that has all of your custom queries in one folder, and finding the particular one that you are looking for can sometimes be a pain? Well, solutions can certainly help you. A solution consists of one or more projects, which then contain files. In SSMS, the project can contain Connections, Queries and Miscellaneous files. These various projects can be used to group your queries so that they are easily accessible.

Personally, I maintain two different solutions. One is for all the presentations that I do, each in a separate project within that solution. The other is all of my day-to-day scripts.

The first step in using a solution is to open up the Solution Explorer window. This is available from the View Menu | Solution Explorer, or by pressing the keyboard shortcut Ctrl+Alt+L.

Day two shows you how to split the screen in SSMS so you can view two sections of the same script at the same time.

Day three shows you how to create tab groups, so you can see two scripts at the same time.

Comments closed

Shane O’Neill shares a few methods for preventing accidental script runs:

Recently I came across a question in Stack Overflow (SO) that said the following:

The other day I was trying to hit another button on the menu but hit Execute – which executed the whole code and ended up deleting some tables. I have always found this scary that hitting one button can execute the whole code.

I want SQL Server to execute code only when something is selected. Is it possible? Or can SQL Server prompt before executing a query?

I thought this is a great question because it can be answered in 4 different ways…

Click through for those four methods.

Comments closed