18 September 2017
18 September 2017

The SQL Prompt Functionality Finder

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.

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

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.

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

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,
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
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
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 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 in SQL Server Management Studio and Visual Studio.

Find out more

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.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Hub

Finding code smells using SQL Prompt: procedures that lack schema-qualification

SQL Prompt implements a static code analysis rule, PE001, which will check code automatically, during development and testing work, for occurrences of a stored procedure being called, via the EXECUTE ...

Also in Product learning

How SQL Monitor Monitors Azure-based SQL Server Failover Cluster Instances

If you're working with SQL Server, one of the most appealing features of cloud-based computing services, such as Azure and AWS, is that they make it so much easier to design systems that are both high...

Also in SQL Prompt

Why your Development team needs SQL Prompt

Alongside specialists, an IT department needs people who are broadly skilled and adaptable, who can pivot quickly to new projects, slot easily into new teams, and be productive quickly.

SQL Prompt is...

Also about Intellisense

SQL Prompt Hidden Gem: Auto-fill the GROUP BY clause

"Have you seen this new feature that auto-fills the GROUP BY with non-aggregated columns!" exclaimed my co-worker, soon after she had installed a new version of SSMS. I hadn't, but I was intrigued. I ...

Also about SQL code formatting

Using multiple custom SQL formatting styles in SQL Prompt

Usually, when coding SQL, you will want to go along with the coding style of the team you are working with. In my previous articles on SQL Prompt, I explained why a team of programmers might want to a...

Also about SQL Prompt

How you can improve your SQL with code analysis in SQL Prompt

What is code analysis?

Code analysis is a formal automated process of scanning a piece of software code and deducing potential problems, issues and faults that may not be apparent to programmers at f...