Phil Factor

18 September 2017

• SQL Prompt
• SQL Formatting and Styles
• Exploring and Refactoring Databases

The SQL Prompt Functionality Finder

As soon as you open SQL Prompt and start typing, its IntelliSense suggestion window will pop up, offering context-specific advice to help you write your query quickly. It will help you select the tables, pick the columns to return, fill out JOIN, WHERE, GROUP BY and other clauses, as well as allow you access to existing function, stored procedures and SQL Prompt snippets.

Figure 1

However, besides IntelliSense, SQL Prompt also offers lots of other time-saving utilities for formatting, refactoring and navigating your SQL code. However, it isn’t always obvious how to get to all the different bits of functionality, because you can find them in a variety of places, including keyboard shortcuts, menu items, context menu items and action lists.

This “functionality locator” will describe, and then summarize in a handy table, what functionality you can find where. Most of it can be accessed from the Action List, the right-click Context Menu or the SQL Prompt menu. See also the SQL Prompt Treasure Island.

The Action List

Highlight some SQL text in an SSMS query window and a red icon appears, which is the action list. As shown in Figure 2, it lists any snippets that can be applied to the selected block of text (such as TRY…CATCH, or BEGIN…END), with their shortcuts, and then all the actions Prompt can perform on the selected text.

Figure 2

The right-click Context Menu

Within the query window, you can right-click at any cursor position, or when hovering the cursor over an object, to bring up the context menu, as shown in Figure 3.

In the middle of the screen, you will find a few SQL Prompt navigational and scripting tasks that you can perform, depending on the exact context of the right click. For example, if you right-click on a table or column name, you can select it in the Object Explorer. If your cursor is within, or at the start or end, of a SQL statement, you can execute it using Execute Current Statement. If you right click on a view, stored procedure, trigger or function you can script it out as an ALTER statement.

Figure 3

If you highlight some SQL code and then right-click, you’ll see an additional item in the context menu, to create a snippet from the selected text.

Figure 4

Note that the context menu for the object browser pane is different. If you right-click on a SQL Server instance name, or on a database, then you’ll find the SQL Prompt functionality to set the tab color for that database or instance, as well as to find invalid objects within a given database (for example, a referenced object that no longer exists).

Figure 5

Finally, within an SSMS results pane, the right-click context menu provides features to ‘refactor’ query results into code, using Copy as IN clause or Script as INSERT, as well as to export results to Excel, for reporting, using Open in Excel.

The SQL Prompt Menu

The SQL Prompt menu offers a whole range of functionality, and is accessed from the SSMS menu by clicking on the SQL Prompt main-menu item, and looks as shown in Figure 6.

Figure 6

From this menu, you can run the Format SQL command, which will format the selected code (or all the code in the window) per the style options defined in the current Active Style. You can define styles, and set a range of other options, using the Options… menu item lower down.

You can also run, from the SQL Prompt menu, various actions to format your code, as well as features for navigating the code, scripting and refactoring.

ToolTip

When you move your cursor over a column in a SQL expression that references columns, you will get a URL in a Tooltip window. Click on it and it will display all the dependencies on that column. Figure 5 shows an example from Adventureworks, with the cursor placed over the EmployeeID column reference in the valid SELECT expression.

All the objects referencing (using) the column are listed, as are the objects that a foreign key references. This functionality in SQL Prompt is only accessible via a Tooltip.

Figure 7

Where it is, and what it does

We can summarize all this in a table:

Although this gives access to all the main functionality, there are some features that must be accessed a different way. Firstly are the two features accessible from the context menu in the browser pane.

And finally, there is the ‘Dependencies’ tooltip.