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
GROUP BY and other clauses, as well as allow you access to existing function, stored procedures and SQL Prompt snippets.
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
BEGIN…END), with their shortcuts, and then all the actions Prompt can perform on the selected text.
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
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.
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).
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.
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.
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.
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
|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.|
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.
Also in Hub
The right to be forgotten is one of the main features of new data protection legislation across the globe. Under Article 17 of one such piece of legislation in Europe, the GDPR, individuals have the r...
Also in SQL Prompt
The syntax of the TOP clause insists on a bracketed, or parenthesized, expression as its first argument, so that TOP 34 should, strictly speaking be TOP (34), although if you are just supplying a nume...
Also in Product learning
Most organizations are finding that the size and number of databases that need to be monitored is increasing, unlike the number of people available to do the work. Now that it is possible for the orga...
Also about SQL Prompt
Code analysis is a useful technique for identifying potential issues in your code base, and ensuring that it adheres to your pre-defined standards, so that you can release your software product with m...
Also about SQL code formatting
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 Intellisense
"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 ...