Product articles SQL Prompt SQL Code Snippets
The SQL Prompt Functionality…

The SQL Prompt Functionality Finder

You have SQL Prompt, but are you aware of all the things it can do and how to get it to do it? Phil Factor provides a handy table to make it obvious.

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.

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.

SQL Prompt IntelliSense

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.

SQL Prompt Action List

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.

SQL Prompt right-click context menu

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.

SQL Prompt Actions, Create Snippet

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).

SQL Prompt tab colors

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.

SQL Prompt menu

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.

SQL Prompt Tooltips

Figure 7

Where it is, and what it does

We can summarize all this in a table:

Label Actions List Context menu Prompt menu Keyboard Shortcut What it does
Active Style No Yes Yes Displays a submenu that allows you to select a formatting style from any current styles.
Add Commas Yes No No Adds commas and quotes to a set of values, one per row,
Add Quotes and Commas Yes No No Adds commas and quotes to a set of values, one per row
Add Square Brackets Yes No No Adds square brackets to all identifiers. Only available if Add brackets to all identifiers option is activated, and will remain available until either the default settings are restored, or Remove unnecessary square brackets option is activated
Comment Yes No No Comment selected text with the ‘double- dash’ line comment at the start of the line
Apply Casing Options Yes No Yes Ctrl+B,Ctrl+U Apply to the selected text whatever casing options have been set
Comment Yes No No Comment selected text with the ‘double- dash’ line comment at the start of the line
Copy as IN clause No Yes No Format values from a single column in a result set into a list, for use in the IN clause of a query
Create Snippet Yes Yes No Opens the snippet dialog box, using the selected text as the snippet’s code and the first letter of each word as the name
Disable Formatting for Selected Text Yes No No Adds comments before and after the selected text to prevent that block from being formatted.
Edit Formatting Styles… No No Yes Displays the ‘formatting styles’ dialog box
Enable Suggestions No No Yes Ctrl+Shift+P Enable the suggestions box so that it appears after a specified time
Encapsulate as (New) Stored Procedure… Yes No Yes Ctrl+B,Ctrl+E Show the dialog box that allows you to encapsulate the selected text as a stored procedure with any variables added as parameters
Execute Current Statement No Yes No Shift+F5 Execute selected text as with Shift F5
Expand Wildcards Yes No Yes Ctrl+B,Ctrl+W Replaces Select * wildcard with the entire column list
Open in Excel No Yes No Export selected query results to Excel
Find Invalid Objects No No Yes Opens a new browser pane with a list of all the invalid objects in the database
Find Unused Variables and Parameters No No Yes Ctrl+B,Ctrl+F Finds unused variables and parameters, and underlines the variable with a tooltip-based error message on mouse-over.
Format SQL Yes Yes Yes Formats the selected SQL or the whole pane.
Help No No Yes Shows a secondary menu with all the help-related features and options.
Inline EXEC No Yes No Ctrl+B,Ctrl+F If you highlight an EXEC command for a stored procedure, scalar function or sp_executesql function, in a query window, it replaces it with the equivalent inline code.
Insert Semicolons Yes No Yes Ctrl+B,Ctrl+C Inserts semicolons at the end of every statement if they have been omitted
Options… No No Yes Displays the Options dialog box to allow you change SQL Prompt’s options.
Qualify Object Names Yes No Yes Ctrl+B,Ctrl+Q Adds the extra information to the name to identify its parentage (table-source for a column, schema for table etc.)
Refresh Suggestions No No Yes Ctrl+Shift+D Update the contents of the location-sensitive suggestion box to reflect the cursor position
Remove Comments Yes No No Removes the inline comments symbol ‘–‘ from the stat of all selected lines
Remove Square Brackets Yes No Yes Ctrl+B,Ctrl+B Removes all unnecessary square brackets from the code selected
Rename Alias/Variable No Yes No F2 If the cursor is positioned on an alias or variable, you can then tap in the new name and all occurrences in the query window are changed
Script as INSERT No Yes No For a results grid, single cell or block of cells, or adjacent values in a single column, converts the selected values into a multi-row INSERT INTO…VALUES expression
Script Object as ALTER No Yes Yes F12 If the name of a function or procedure is selected, this creates a new query window with the script of the routine
Select in Object Explorer No Yes Yes Ctrl+F12 If you select an object and click on this menu item, it will (in certain contexts) select that object in the object explorer window
Smart Rename… No No Yes Shows the ‘Smart Rename’ dialog box.
Snippet Manager… No No Yes Displays the ‘Snippet Manager’ dialog box.
Split Table… No No Yes Displays the ‘Split Table’ dialog box.
Summarize Script No No Yes Ctrl+B,Ctrl+S Displays a resizable contents summary dialog box that allows you to select a keyword to highlight the keyword or the corresponding block of code in the script editor.
Uncomment Yes No No Same as ‘remove comments’. Removes inline comment symbol at start of line
Unformat Yes No No Removes all formatting whitespace from the selected code

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.

Tab Color (Server) No No No Assign particular colors for the query windows when connected to different server environments.
Find Invalid Objects No No No Lists all the invalid objects in the selected database.

And finally, there is the ‘Dependencies’ tooltip.

Dependencies No No No Lists all the objects referencing (using) the column and being referenced by the column.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more