This document provides a high-level overview of the installation, set-up and getting started, with respect to the demo 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.
) and Date Expressions into building up their shortcuts.
Scope
The scope of this document is to convey the processes involved to get started with SQL Shortcuts.
To purchase the full working version of SQL Shortcuts, go to Gumroad and search for the appropriate SSMS extension version you need to purchase:
- SSMS 20 - SQL Shortcuts
- SSMS 19 - SQL Shortcuts
- SSMS 18 - SQL Shortcuts
Demo - Limited Version
You can use all the features of the full version in the demo version, but you will be limited to only one shortcut and the SQL Statement\Expression will have a max of 100 characters, but this will be enough for you to get an idea if the extension is something that will aid your SQL development.
NB: Currently the demo version will only work with SSMS 20.
Installation
Unzipping Extension
Once you have downloaded the VSIX extension from Microsoft’s marketplace, do NOT double click on the VSIX file, as this is not an extension for Visual Studio, but for SSMS (VSIX defaults to a Visual Studio installation).
Right click and Unzip the VSIX file, to extract the folder:
Once the folder has been extracted, copy it to your SSMS 20 extension installation folder, by default, SSMS 20 will be installed here:
C:\Program Files (x86)\Microsoft SQL Server Management Studio 20\Common7\IDE\Extensions\
NB: If you installed SSMS to a different folder, you would need to copy the extracted (SQL Shortcuts extension) folder to that Extension path.
Also, you might need network permissions to perform the copy, this might need to be granted by a network administrator!
User Guide
Now open the version of SSMS that is associated with your SQL Shortcuts extension, by clicking on the desktop icon or finding it through the Start Menu:
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!
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.
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 predefined shortcut 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.
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.DayOfWeek}
To evaluate your SQL Expression (in either screens, click on the associated Test button)
Create a New SQL Shortcut
From the SQL Expression Shortcut screen, click on the Add New Shortcut button (if it is disabled, it’s because you are limited to 1 SQL Shortcut – delete the existing shortcut to add a new one).
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 backticks (``) that evaluate 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 Backtick key.
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}’, this will fail when evaluated, but if you enter the correct C# syntax ‘{DateTime.Now}’ 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.
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.
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:
Executing SQL Shortcut
As described earlier in Create A New SQL Shortcut, you must type the backtick 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):
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
Example of Arabic Screens
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.
Incorrect Version
If you copy the unzipped SQL Shortcuts folder into any other SSMS version extension folder other than the appropriated SSMS version – you will get a reminder when SSMS starts, that the extension is not for this version.
Definitions, Acronyms, and Abbreviations
| Acronyms |
Definition |
| SQL |
Structured Query Language |
| CRUD |
Create Retrieve Update Delete |
| SMO |
SQLServer Management Object |
|
