Category: SQL Server Management Studio

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).

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.

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.

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.

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.

Jen McCown goes through a good example of where regular expressions can solve a DBA’s boring and repetitive task:

What I needed was to be able to check each one of those tables, and see if any of the should-be-deleted rows still exist. Of course, I can certainly type this all out by hand…

IF EXISTS (SELECT * FROM dbo.Table1 WHERE val = 1) SELECT 'Table1 has undeleted rows!' as TableName;
IF EXISTS (SELECT * FROM dbo.Table2 WHERE val = 1) SELECT 'Table2 has undeleted rows!' as TableName;

*Sigh*. I’m already  bored. And in reality, I had 45 of these statements, not 15. I’m not a big fan of repetitive, monkey-button work.

SSMS regular expressions are great.  I got to see a version of Sean’s linked talk in person and it helped things click in my mind.

Bert Wagner shows off vertical selection in SSMS (using the Alt key):

Sometimes when writing an ad hoc query you might want to take the results of one query and put them into an IN() statement of another query.

Sure, you can write a subquery to put into your IN() statement…but that’s too much work for a one-time use disposable query.

What you can do instead is:

1. Copy your values of interest

2. Paste them into your IN() statement

3. Hold down the ALT key while dragging the mouse down in front of all of your pasted values

4. Type a comma (see video above for an easier demonstration).

For SSMS speedrunning strats, you can also hold down ALT + SHIFT and use your keyboard arrow keys instead of using the mouse.