SQL Prompt ALT S Magic

Use the SQL Prompt Command Palette to find and execute 'hidden' SQL Prompt functionality, plus any of the common SSMS commands, as well as search for database objects.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

The command palette in SQL Prompt provides a context-aware way of searching for ‘stuff’ in SSMS. Hit ALT+S, in SSMS, to open up the command palette, and you will see that this ‘stuff’ comprises a smorgasbord list of SQL Prompt commands, SSMS functionality, database objects and system objects. You can quickly choose between Database Objects and Commands and then as you type in the search box, this filters down smoothly to just the objects or commands you need.

SQL Prompt command palette

How does it help me?

How often do you stare hard at SSMS, and wonder where on earth is the functionality you need? Never? Excuse my cynical smile. Well, maybe it’s true, for the stuff you use all the time, but what about the functionality you use just now and again? Perhaps I use too many tools to keep it all in my head, but whatever the reason, I sure need help.

Running SQL Prompt functionality

SQL Prompt is famous for hiding some of its best features. I’ve tried several ways of exposing all the gems including a functionality finder table, a keyboard shortcut diagram, and even a treasure map.

Now, though, help is at hand from directly within SSMS. Let me give an example. Let’s say you have a tricky section of SQL laid out exactly as you need it, and so you want to stop SQL Prompt from trying to auto-format it when you hit Ctrl K+Y. You have it selected:

disable sql formatting

‘But…now…where is the disable formatting command hidden away?’ Scans the main SQL Prompt menu…no, not there. You could search through the documentation, but just want to search for the functionality as if you were using Google. Now, you can.

Hit ALT+S, start typing in “formatting“, get as far as “form…“, and there it is. You can just click on the function you want…

disable formatting command

And the code now has the ‘guard comments’ that prevent SQL Prompt from formatting that code you highlighted. See How to Apply Non-Standard SQL Formatting Using SQL Prompt for more.

All of SQL Prompt’s menu and action list items are directly runnable from the command palette, and will do exactly the same as running them from the menus. So, that helps me uncover all of SQL Prompt’s hidden treasures. What about SSMS, which isn’t the easiest tool to navigate?

Getting at SSMS functionality

Thinks…”why aren’t my registered servers showing up in SSMS?

Hit ALT+S, type in “Registered servers“, get as far as “regis“:

where is the registered servers window

I’ve clicked on it and…

local registered servers

…I’m now able to select one of my local registered servers.

All the commonly used menu items from SSMS are exposed as commands in the command palette. You can run them straight from the palette and they will do exactly the same as running them from the menus.

Searching a database?

When your query window gains a connection to a database, SQL Prompt will get the metadata of the database. With the focus on this query window, you can use the Database Objects section of the command palette to review and search for database objects. By clicking an item in the list, you select the corresponding object in the SSMS Object Explorer pane.

Imagine that you need to find a utility stored procedure in the database that allows you to create an ER diagram. You connect to the database, and press ALT+S:

searching for database objects

You can remember only that it is called <something>PlantUML<something>, so you start typing.

finding a stored procedure

Click on the one item in the list, and you can modify it, execute it, or examine it in Object Explorer:

modify a stored procedure in SSMS object explorer

When the command palette is opened, and a query editor window has focus and is connected to a database, all objects from the schema of the database will appear in the command palette. Database objects have an icon that represents the type of object, followed by their name, which is the object you are searching for. On the right-hand side you’ll find its parent object, so for a column it would be a table, for a table it will be a schema and for a schema it will be a database.

There are a few inconsistencies in the current release. Default constraints don’t seem to have the table to which they belong, though other constraints do. Database users are also listed as schemas. Schemas such as information_schema is listed as a user.

Summary

If you hold in your head the location of every SQL Prompt and SSMS commands you’ll ever need, then you won’t have much need for the command palette. However, for the rest of humanity without special geeky powers, it is a useful tool.

A Graphical User Interface is essential for any tool that you need to use without constantly referring to help text. However, using a GUI doesn’t guarantee you an interface that is usable. It is necessary but not sufficient. There is now so much functionality in both tools that navigation is a problem, and this Command Palette cuts the Gordian Knot: It solves the problem by circumventing it.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more