In SQL Server Management Studio (SSMS), there are a lot of options and functions. In fact, there are so many that you might never see / encounter quite a few of them. Part of the issue is that there are four locations to find these options and functions. The four locations are:

• Menu bar (i.e. “File”, “Edit”, etc)
• Toolbar buttons (just below the Menu bar)
• Context menus (i.e. right-click on something)
• Shortcut keys

And, there are options and functions that you are aware of and use, but you can only find them in one place and might prefer to find one or more of them in another location.

One of those options, for me at least, is SQLCMD Mode. For those who might be unfamiliar with it, SQLCMD Mode is the ability to have SSMS interpret / process a subset of the SQLCMD commands that can be added to SQL scripts for processing via the sqlcmd.exe command-line utility. SQLCMD commands are processed by the client (i.e. sqlcmd.exe or SSMS) instead of by SQL Server, and the subset of those commands that are available in SSMS can be found here: Edit SQLCMD Scripts with Query Editor: Supported SQLCMD Syntax.

SQLCMD mode is off by default and can be found only in the Query menu. While it is possible to have it enabled by default, I’ve never heard of anyone actually doing that (probably due to IntelliSense and the Transact-SQL debugger not working when SQLCMD mode is enabled). The image below shows the Query menu with SQLCMD Mode highlighted. Please note that there is no shortcut key indicated on the right side of the menu (see red arrow).

While I could use the key sequence of ALT + Q and then M, I would prefer a single-key shortcut. Fortunately, this is easy enough to do.

How to Assign a Shortcut Key

Go to the Tools menu and select Options…:

In the Options dialog that pops up, on the left side, navigate to Environment and then to Keyboard as shown below:

Then, on the right side, you will first place the cursor in the text field for Show commands containing: and type in “sqlc”. Doing that will narrow the list below that text field to a single item for “Query.SQLCMDMode”. Next, in the Use new shortcut in: drop-down, select “SQL Query Editor” (there’s no need to set this as a “Global” shortcut since SQLCMD Mode doesn’t apply to anything but SQL scripts). Then, in the text field for Press shortcut keys: type CONTROL + ` (backwards single-quote, under the “~”). Then click the Assign button, and finally click the OK button. These steps are illustrated below:

If you go back to the Query menu now, you should see the following:

The red arrow points to the shortcut key that we assigned a moment ago. And you can test it easily by typing in “:r” into the query editor and hitting CONTROL + ` to toggle SQLCMD Mode on and off. As you do, the “:r” will go from a white background (SQLCMD Mode disabled) to a grey background (SQLCMD Mode enabled) and back again.

Isn’t that awesome? Yes, it is. But wait, there’s more!

Assigning More Functions

There are two other functions that are, by default, only available via the context menu that appears when right-clicking on a Query Editor tab: Copy Full Path and Open Containing Folder:

As you can see in the image above, there are no shortcut keys defined for either of these functions. Thankfully, these are also easy to assign:

1. Start at the top of the “How to Assign a Shortcut Key” section above.
2. Instead of typing in “sqlc”, use “conta” to narrow the list down to “File.OpenContainingFolder”.
3. I use CONTROL + ' (single-quote / apostrophe), in SSMS 2017, for Open Containing Folder.
4. After you click Assign, you don’t need to close the dialog in order to assign another shortcut key. Just place the cursor in the **Show commands containing:” text field and remove whatever is there.
5. Next, type in “copyf” to narrow the list down to “File.CopyFullPath”.
6. I use CONTROL + , (comma), in SSMS 2017, for Copy Full Path. If this key sequence is already in use (something will appear in the Shortcut currently used by: drop-down) as it is in SSMS 2012 and 2014, then hit BACK SPACE to remove that key sequence and instead try CONTROL + ; (semicolon).
7. Once these have been assigned, then you can click the OK button to close the dialog.

If you right-click on a SQL Query Editor tab now, you will see the following:

You can test both of these new shortcut keys out by placing the cursor in a SQL Query Editor window (even if it is a new, empty script). Hitting CONTROL + ' will open a File Explorer window to the folder that the script is in. For a new, unsaved script, the folder will be something along the lines of: C:\Users\_your_windows_login_\AppData\Local\Temp.

For testing Copy Full Path we need to keep in mind that it is copying the path to the clipboard, so we will need to paste from the clipboard after doing the copy to verify that it worked. With the cursor in the Query Editor, hit CONTROL + , and then CONTROL + v. You should see something along the lines of the following in the Query Editor: C:\Users\_your_windows_login_\AppData\Local\Temp\~vs68C5.sql

Important Note About Key Sequences

1. When assigning shortcut keys, you are allowed to assign two-key sequences. If you ever want to use a two-key sequence, please make sure that upon hitting the first key in the sequence that you are not creating a conflict with an existing single-key sequence. You will be able to determine this by reviewing whatever appears in the Shortcut currently used by: drop-down. For example, creating a two-key sequence that starts with CONTROL + c is a bad idea as it will break the existing operation of copying to the clipboard.

2. Not all options and functions are available for shortcut keys. For example, Disconnect All Queries is available in the Menu Bar and Context Menu, but there is no “command” for it to map a shortcut key to.

Next Time

In the next SSMS Tip we will see how to add functions and options to the Menu Bar, Toolbar, and Context menus, including items such as Disconnect All Queries that are not available for shortcut keys.