28 November 2019

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.

28 November 2019

SQL Prompt by Keyboard

Phil Factor shares a handy keyboard shortcut diagram that reveals some of the most useful of Prompt's keyboard shortcuts, and a wallchart showing every piece of Prompt functionality, what it does, where to find it in the various menus, and of course its keyboard shortcut.

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.

When you need to work quickly with SQL Prompt, the most obvious time-savers are the keyboard shortcuts. Certainly, when I’m programming in SQL, I really don’t want to have to point and click the whole time so there really isn’t much of an alternative to learning those keystrokes. Ironically, currently the most straightforward way of learning the shortcuts is to from the point-and click menu options; the Prompt menu, and the context menu are where most of them are shown. Even then, there are some things that you just have to know.

I’ll provide some diagrams to help to…err…prompt you with the right keystrokes when you find yourself having to reach for the mouse when it isn’t necessary. Both diagrams are available to download, as PDF wallcharts, at the bottom of the article.

The Essential Keyboard Shortcuts

Having selected some code in a query pane in SSMS, the most useful key to hit is Ctrl. This gets you to the Prompt Action menu, and by tapping the relevant keys, you can bring up the action you want and then tap Enter. This gets you a long way, and it pays to avoid these useful key sequences when assigning snippet codes to snippets.

Controversially, perhaps, I keep Prompt’s Suggestions box and column picker in “quiet mode” (enabled, but set not to pop up automatically). I can then request them “on demand”, using Ctrl+Space, whenever I find that I want suggestions, which is often.

When I’m doing SELECT statements, I like pressing Tab to expand wildcards. To do this, you must give Prompt the context. Write the SELECT statement, including the FROM clause, but missing out the column list. Go back and write the wildcard character ‘*’ and then hit the Tab Key. Whoosh! In goes the entire column list, qualified with the name or alias of the origin table source.

I use the Tab History feature a lot. To invoke that. You quickly learn Ctrl+Q to see the contents of the open tabs. You can then flick between the Tab history and the SSMS windows with the Alt key. The real power comes when you learn to navigate between All Tabs, Open and Closed with Ctrl and either or , and navigate between tabs with and .

The Keystroke Keyboard Diagram

If you are visually oriented, like me, a diagram is a nice way to learn these keystrokes and helps you to see the patterns. For example, it shows how Ctrl+B is the gateway to a whole range of reformatting and refactoring functionality.

I’ve highlighted the letters that provide the mnemonic that helps you to remember the right set of characters to hit on that keyboard.

The Keystroke Taxonomy

There is a lot of functionality in SQL Prompt, and a lot of this functionality can be accessed via keystrokes. The keyboard diagram is useful for some of the common actions, but in the end, you need a list, because there is too much there. Here’s the table showing all the Prompt functionality by menu and by keystroke. The Wallchart PDF, which you can download at the bottom, is much prettier….

Label Actions List Context menu Prompt menu Keyboard Shortcut What it does
Enable Code Analysis Alt+O,E,cr Enable or disable the code analysis. This is a toggle Code Analysis
Manage Code Analysis rules Alt+O,M Access the window that allows you to choose the code analysis rules that you wish to use
Open Issue Details Ctrl (hover over squiggly green underline area first) Open the window listing the issue details
Enable Suggestions Ctrl+Shift+P Enable the suggestions box so that it appears after a specified time Code completion
Move up/down suggestions list or navigate up or down the list of suggestions once the suggestions list has the focus
Move up/down the suggestions box filters Ctrl+↑ or Ctrl+ navigate up or down the list of filters for suggestions once it has the focus
Refresh Suggestions Ctrl+Shift+D Update the contents of the location-sensitive suggestion box to reflect the cursor position
Show Suggestions Ctrl+Space Show the suggestions box
Switch to/from column picker Ctrl+ ← or Ctrl+ → Switch to/from column picker
Active Style Displays a submenu that allows you to select a formatting style from any current styles. Formatting and refactoring
Add Commas Ctrl,A,C,Enter Adds commas to a set of values, one per row, Select text range first
Add Quotes and Commas Ctrl,A,Q,Enter Adds commas and quotes to a set of values, one per row. Select text first
Add Square Brackets 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
Apply Casing Options Ctrl+B,Ctrl+U Apply to the selected text whatever casing options have been set
Comment out Ctrl,C,O,Enter Comment selected text with the ‘double- dash’ line comment at the start of the line
Copy as IN clause Format values from a single column in a result set into a list, for use in the IN clause of a query. Grid view only
Dependencies Lists all the objects referencing (using) the column and being referenced by the column.
Disable Formatting for Selected Text Ctrl,D,Enter Adds comments before and after the selected text to prevent that block from being formatted.
Edit Formatting Styles… Alt+O,E,E Displays the ‘formatting styles’ dialog box
Encapsulate as (New) Stored Procedure… 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
Expand Wildcards Ctrl+B,Ctrl+W Replaces Select * wildcard with the entire column list
Find Invalid Objects Alt+O,I Opens a new browser pane with a list of all the invalid objects in the database
Format SQL Ctrl+K,Ctrl+Y Formats the selected SQL or the whole pane.
Inline EXEC (Inline Stored Procedure) Ctrl+B,Ctrl+I 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 Ctrl+B,Ctrl+C Inserts semicolons at the end of every statement if they have been omitted
Qualify Object Names Ctrl+B,Ctrl+Q Adds the extra information to the name to identify its parentage (table-source for a column, schema for table etc.)
Remove Comments Ctrl,R,C,Enter Removes the inline comments symbol ‘–’ from the start of all selected lines (select text first)
Remove Square Brackets Ctrl+B,Ctrl+B Removes all unnecessary square brackets from the code selected
Rename Alias/Variable 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 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
Smart Rename… Alt+O,R Shows the ‘Smart Rename’ dialog box.
Split Table… Alt+O,T Displays the ‘Split Table’ dialog box.
Uncomment Ctrl,U,Enter Same as ‘remove comments’. Removes inline comment symbol at start of line
Unformat Ctrl,U,N,F,Enter Removes all formatting whitespace from the selected code
Browse open tabs Ctrl+Q Browse and search open tabs in Tab History Tab Magic
Switch between open, closed and all tabs Ctrl+← or Ctrl+→ (in tab history window) When in Tab History, switch between open, closed and all tabs
Create Snippet Ctrl,C,S,N,Enter Opens the snippet dialog box, using the selected text as the snippet’s code and the first letter of each word as the name Miscellaneous
Help Alt+O,H Shows a secondary menu with all the help-related features and options.
Open Action list Ctrl Open the action list when some text is selected
Options… Alt+O,O Displays the Options dialog box to allow you change SQL Prompt’s options.
Tab Color (Server) Assign particular colors for the query windows when connected to different server environments.
Open in Excel Export selected query results to Excel
Snippet Manager… Alt+O,S Displays the ‘Snippet Manager’ dialog box.
Execute Current Batch Alt+Shift+F5 Execute selected text as with Shift F5 Code Execution
Execute Current Statement Shift+F5 Execute selected text as with Shift F5
Execute to cursor Ctrl+Shift+F5 Execute to cursor
Find Unused Variables and Parameters Ctrl+B,Ctrl+F Finds unused variables and parameters, and underlines the variable with a tooltip-based error message on mouse-over. Navigation
Script Object as ALTER 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 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
Summarize Script 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.

Summary

The SQL Prompt keyboard shortcuts are important, but they aren’t always the sort of thing you discover by using Prompt and they aren’t always easy to remember even if you do discover them. I’ve tried to show how you can get to grips with learning them by wallcharts instead, with the assistance of mnemonics where possible. If I’ve managed to introduce you to some SQL Prompt functionality that you haven’t yet discovered, then that is a bonus.

 

Downloads

Share this post.

You may also like