Writing a SQL Operations Studio Extension in 15 minutes
SQL Operations Studio is a modern, extensible developer/DBA tool for building and managing your SQL Server databases. Every month brings new features, and with the March release came a particularly exciting one — extensions! Writing your own is easier than you might think, so I set the goal of building one in under 15 minutes.
The extension: SSMS Keymaps
I’d seen a lot of tweets and other feedback from users wanting to move from SSMS to Ops Studio but struggling with key bindings:
Happily this is something Visual Studio Code ran into and fixed already — they have Sublime, Notepad++ and other Keymap extensions that give users the keyboard shortcuts they expect to “just work”. Since Ops Studio is build on VSCode we can build an SSMS Keymaps extension to do the same thing!
TL;DR — where’s this extension? Can I add shortcuts? How did you build it?
Can I add shortcuts: Contributions are very much welcome — please read the Readme and add your own shortcuts!
How did you build it — here’s the short version:
- I installed some stuff (one time only),
- Ran a template generator that creates an extension project,
- Picked some keyboard shorctuts you want to use in Ops Studio,
- Added them to a special file called
- Used a tool to package it into a
- Then shared it with the world!
The rest of this post is these steps, just with a lot more details.
Creating a new extension project
We’ll build our extension in Visual Studio Code. If you run into trouble you can read their extension guide — as Ops Studio is built on the same framework, most things should “just work”. To get started, you need a few things:
- Visual Studio Code
- Node.js installed and added to your path
- SQL Operations Studio Debug extension (optional). This lets you test your extension without packaging it and installing into Ops Studio
It’ll take about 10 minutes to install all of the above, so we only have 5 minutes left to get our extension going. Luckily it’s pretty easy!
Install the extension generator
We’ve built an extension generator using Yeoman. To install it, run the following from the command prompt:
npm install -g yo generator-sqlops
This walks you through the steps to create your extension.
Create your extension
Run the following command to launch the extension generator:
New Keymap from the list of extension types.
Follow the steps to fill in the extension name (in our case,
ssmskeymap) and a description and it’ll create a new folder.
Open the folder in Visual Studio Code and you’re ready to create your own keybindings!
Adding a keyboard shortcut
Step 1: Find the shortcuts to replace
Now that we have our extension ready to go, I want to add some keyboard shortcuts (or keybindings) used in SSMS into Ops Studio. I used Andy Mallon’s Cheatsheet and RedGate’s keyboard shortcuts list for inspiration. The top things I saw were missing were:
- Run a query with the actual execution plan enabled. This is
Ctrl+Min SSMS and doesn’t have a binding in Ops Studio.
Ctrl+Shift+Eas a 2nd way of running a query. Some people complained this was missing
Alt+F1run sp_help. We added this in Ops Studio but since that binding was in use we put it as
- Toggle full screen (
F8to show your Object Explorer / Servers view
It’s easy to find and replace these — run
Open Keyboard Shortcuts to show the Keyboard Shortcuts tab in Ops Studio, search for
query and then choose to Change Keybinding. Once you’ve done so you can see it in the keybindings.json file (run
Open Keyboard Shortcuts File to see it).
Step 2: Add shortcuts to the extension
This one’s easy — I opened up the package.json file in the extension and replaced the
contributes section with the following:
Step 3: Test
To test, I made sure
sqlops was in my PATH by running the
Install 'sqlops'command in PATH command in Ops Studio, and had the SQL Operations Studio Debug extension installed in VSCode. Then I hit F5 to launch Ops Studio in debug mode with my extension running
I opened up a new SQL query, typed in
select * from sys.tables and hit
Ctrl+M to run it and see the execution plan. And…. tada! It ran as expected!
Success! It’s all working and ready to share. Keymaps are one of the quickest extensions so feel free to make your own!
Packaging the extension
To share with others I needed to package the extension into a single file. This can be published in an extension marketplace or just shared among your team / community. To do this, I first needed to install another npm package from the command line:
npm install -g vsce
Then I ran
vsce package from the base directory of my extension. I did have to add in a couple of extra lines to stop the vsce tool complaining:
Once this was done, my
ssmskeymap-0.1.0.vsix file was created and ready to install / share with the world!
Publishing your extension to a marketplace
As this is early days, SQL Operations Studio is just rolling out the process for publishing to the Extensions list inside Ops Studio. We’ll be documenting the exact steps in our wiki, and the basic process will be to host the extension VSIX somewhere (a Github Release page is a good idea) then submit a PR changing this JSON file to add you extension info.
I’ll be following this process myself to get the SSMS Keymaps extension published and update this page once I’ve completed it!
Next Steps — contributing to SQL Operations Studio
I hope after reading this you’ll be inspired to build your own extension for SQL Operations Studio. We have support for Dashboard Insights (pretty graphs that run against your SQL Server), a number of SQL-specific APIs, and a huge existing set of extension points inherited from Visual Studio Code.