SQL_Shortcuts_SSMS21Plus

SSMS 21+ SQL Shortcuts Extension

1. Introduction

This document provides a high-level overview of the installation, set-up and getting started, with respect to the SSMS extension SQL Shortcuts. We’ll go through the extracting of the extension, where to install it based on your version of SSMS, how to know it has been installed correctly and then using SQL Shortcuts from within SSMS.

1.1. Purpose

To provide a comprehensive overview of your new SSMS extension (SQL Shortcuts), present the user with a one-stop guide, which they can refer to if they have any issues. Inform the user on how to incorporate table tag () and Date Expressions into building up their shortcuts.

1.2. Scope

The scope of this document is to convey the processes involved to get started with SQL Shortcuts for SSMS 21+ (only).

2. Installation

Download the extension from Microsoft’s Marketplace:

Once you have downloaded the VSIX installer and closed any versions of SSMS you may have open.

Double clicking the installer to initiate the installation process within your environment, the installer will pick up what versions of SSMS you have installed, and which are compatible with the new extension.

For example, below I have both SSMS 21 and 22 installed, and can deploy the extension to both versions at the same time.

NB: You might need network permissions to perform this action.

3. User Guide

Now open your version of SSMS, by clicking on the desktop icon or finding it through the Start Menu (e.g. of SSMS 22 below):

You will know that your extension has been loaded when you see the status bar on SSMS itself updated to show this message (which is the path to SQL Shortcut’s log file):

NB: You can still use SSMS as normal, without having to wait for the extension to load!

3.1. Table\View Selection Monitoring

SQL Shortcuts allows you to dynamically create shortcuts that will incorporate the most recently highlighted Table or View from the SMO (SQL Management Object) component (below, I have selected the Humanresources.Employee table) — this functionality will not interfere with any of SSMS’s existing features. You will see later, when we run a shortcut, how this feature can come in handy.

3.2. Configure SQL Shortcuts

From the Tools menu, select the option Configure SQL Shortcuts:

This will then display the SQL Expression Shortcuts screen, from here you can manage & test existing shortcuts, export to PDF or create new ones.

By default, if this is your first time using the extension, a number of (predefined) shortcuts will be loaded to get you up and running. Once you edit, delete or add new shortcuts, they will be persisted to file — which will be loaded the next time you use SQL Shortcuts.

NB: CRUD actions are saved automatically to file, so you don’t have to worry about saving changes. Plus, the form’s status-bar will indicate where the shortcuts have been saved too.

If you click on the Edit icon for a row, the Edit SQL Shortcut screen will be displayed, from here you can update the SQL syntax (and test it), rename the shortcut key (that you will type into the SQL editor to render the SQL Statement, in its place):

When building up your SQL Statement, you can also use the Table Tag and the Date Expression buttons to inject generality (based on the selected table form the SMO) or date related logic.

I can build up a more complex query with the dynamic table name or Date Expressions, and use it as a template to inject the table name:

NB: The date logic is C# (.Net) oriented, and in general if you add any C# expression that can be evaluated between the Opening & Closing Curly Brackets, the SQL Shortcut expression when rendered will try to evaluate it — so this is allowing you to mix SQL and C# in a single statement, for e.g. I am using C# logic between the curly brackets, that will evaluate to a value when rendered (see popup box for SQL statement that will get rendered in SQL editor):

SELECT DATEPART(WEEKDAY, [BirthDate]) AS DayOfWeekBorn

FROM [AdventureWorks2014].[HumanResources].[Employee]

_WHERE DATEPART(WEEKDAY, [BirthDate]) > {[DateTime.Now](http://DateTime.Now" \t "blank).DayOfWeek}

To evaluate your SQL Expression (in either screens, click on the associated Test button)

3.3. Create a New SQL Shortcut

From the SQL Expression Shortcut screen, click on the Add New Shortcut button.

This will display the New SQL Shortcut Expression screen, from here you can create a simple shortcut that you constantly find yourself having to type into the SQL editor pane.

For e.g. Select * From

But you can elaborate on this, but inserting the Table Tag, which will use the last selected table\view from the SMO and inject it at runtime into your SQL expression.

So, for e.g. if I create a SQL shortcut like: Select * From

Where and give it the shortcut name st and save it.

If I now select a table in the SMO:

And then go to the SQL editor and type in st followed by two backticks (st``) the new expression will be rendered with the selected table form the SMO:

The shortcut will be rendered from this (st``)

To the SQL expression:

NB: It’s the two back-ticks (``) that evaluates the shortcut into its equivalent expression, see the key below (usually top left on your keyboard — this key has no TSQL significance). You also don’t need to press any combination of keys to render the back-tick key.

3.4. Date Expressions

As dates are very common in SQL, the Date Expression dropdown button has been added to quickly add an appropriate date expression (in the format that the compiler is expecting between two curly brackets and single quotes around it for the SQL string comparison).

NB: You can build up your own Date expressions, or any C# expression, but keep in mind that it has to go between the curly brackets and the C# syntax is case sensitive.

For e.g. is you manually enter ‘{[Datetime.Now](http://Datetime.Now" \t "_blank)}’, this will fail when evaluated, but if you enter the correct C# syntax ‘{[Date](http://DateTime.Now" \t "_blank)[T](http://DateTime.Now" \t "_blank)[ime.Now](http://DateTime.Now" \t "_blank)}’ it will evaluate correctly. The expression builder has been designed to be very flexible, but you need to have the syntax correct — thus, use the Test buttons to evaluate before saving.

3.5. Validating Input

When entering a SQL Shortcut key, it must be unique, in the screenshot below you can see that the shortcut w was entered but it was immediately flagged because it is already being used. You won’t be able to save the shortcut if the input is invalid or not complete.

The SQL Test button will be disabled until the user enters text into the SQL Statement box.

3.6. Exporting SQL Shortcuts to PDF

If you wish to share your SQL Shortcuts or keep a hardcopy of them, you can export them to PDF. Within the SQL Expression Shortcuts screen, click on the Export Shortcuts button. This will then prompt the user to select a location to save the PDF, the PDF will also be opened in the default browser.

The shortcuts PDF will open in a browser after saving:

A SQL Server notification will also be displayed to prompt the user to the saved location:

3.7. Executing SQL Shortcut

As described earlier in Create A New SQL Shortcut, you must type the back-tick twice (``) to have your shortcut evaluated. For example, if I have a shortcut called w (note that shortcuts are not treated as case-sensitive)

And in the SQL editor, if I type W`` the associated SQL Expression will be evaluated and displayed in the editor (plus if I had of highlighted a table in the SMO, it would have been interpolated the table name):

4. Localized

The application has been translated into several languages — namely:

• Arabic
• Danish
• German
• Greek
• Spanish
• Finnish
• French
• Hindi
• Italian
• Japanese
• Korean
• Dutch
• Norwegian
• Polish
• Portuguese
• Russian
• Swedish
• Turkish
• Chinese

4.1. Example of Arabic Screens

5. Uninstall Extension

To uninstall the extension, from your version of SSMS, simply delete the SQL Shortcuts extension folder.

NB: Ensure that all versions of SSMS are closed prior to deleting the extension folder.