Navigating around Prompt Island

There are several 'landmarks' where you'll find treasure in Prompt. Firstly, there is the SQL Prompt menu.

The Prompt menu contains a whole range of functionality, including running a refactoring, setting options, picking the active formatting style, and performing various actions.

Then there is the Actions List, which is easy to miss but it appears whenever you select some code in a query window, and allows you to perform various types of action on the text you've selected, such as refactoring it or encapsulating it in a snippet.

The right-click context menu provides yet more functionality. If you right-click within SSMS object explorer, you get different functionality from the Query window, according to the context, and you get different functionality again if you right click in the results pane.

Within SSMS object explorer the context menu will provide Tab Management functionality (if a server or database is highlighted), or refactoring functionality (if a database object is highlighted).

Within an SSMS results pane, the right-click context menu provides features to 'refactor' query results into code, as well as to export results to Excel, for reporting.

A lot of Prompt treasure is also available via a secret Prompt keyboard shortcut – see the Prompt Functionality Finder for full details.

The Formatting Forest

SQL Prompt supports built-in and custom 'styles' that you can apply to the code in a query window, or any highlighted portion of that code, to format SQL code exactly as you like it.

Formatting is all about taking some code and making sure that it is easy to read and understand. Formatting changes are 'decorative' changes that affect only white-space, such as line breaks, tabs and spaces; they do not change the meaning of that code in any way. If we remove all formatting from a piece of code, it will continue to function exactly as before.

SQL formatting with Prompt styles

Listing 1 shows some suitably impenetrable code (Thanks to Joe Celko for this).

CREATE TABLE Pilot_Skills ( Pilot_Name CHAR(15) NOT NULL, Plane_Name CHAR(15) NOT NULL, PRIMARY KEY ( Pilot_Name, Plane_Name ) ) CREATE TABLE Hangar ( Plane_Name CHAR(15) NOT NULL PRIMARY KEY ) SELECT DISTINCT PILOT_NAME FROM PILOT_SKILLS AS PS1 WHERE NOT EXISTS(SELECT * FROM HANGAR WHERE NOT EXISTS(SELECT * FROM PILOT_SKILLS AS PS2 WHERE(PS1.PILOT_NAME=PS2.PILOT_NAME)AND(PS2.PLANE_NAME=HANGAR.PLANE_NAME)))

With a few keystrokes, to run SQL Prompt's Format SQL command, the code is formatted as shown in Listing 2. SQL Prompt refers to this as 'Applying the layout'.

CREATE TABLE Pilot_Skills
        (
        Pilot_Name CHAR(15) NOT NULL,
        Plane_Name CHAR(15) NOT NULL,
        PRIMARY KEY
        (
        Pilot_Name,
        Plane_Name
        )
        );

        CREATE TABLE Hangar
        (
        Plane_Name CHAR(15) NOT NULL PRIMARY KEY
        );

        SELECT DISTINCT Pilot_Name
        FROM Pilot_Skills AS ps1
        WHERE NOT EXISTS
        (
        SELECT Plane_Name
            FROM dbo.Hangar
            WHERE NOT EXISTS
            (
            SELECT Pilot_Name, Plane_Name
                FROM Pilot_Skills AS ps2
                WHERE(Pilot_Name = Pilot_Name) AND (Plane_Name = Hangar.Plane_Name)
            )
        );
                            

When we run the Format SQL command, SQL Prompt will format the SQL according to the layout defined by the current Active Style. There are some built-in styles to try out, and it is easy to modify the way that they work. It is best to start with a style that works well enough and then to modify it to your taste as you go along. You can reformat any code that is error-free; SQL Prompt must compile the code to format it, so it can't deal with any code with syntax that it can't understand.

The sharp-eyed explorer will have noticed, in Listing 2, that as well as laying out the SQL nicely with spaces and line-breaks, Prompt has made some additional code changes; it's added semicolons, and changed the case of the identifiers to be the same as in the initial CREATE statement. This is because we can configure the Format SQL command to apply additional Actions, at the same time as applying the current formatting style. We'll cover Actions when we reach Refactoring Redoubt.

Controlling Formatting

There are several useful commands that allow us to apply and control code formatting in SQL Prompt. We apply formatting using the Format SQL command, reached from the Actions List, Context menu, Prompt menu, or keyboard Ctrl (K, Y).

Occasionally, your code is laid out in a special way that mustn't be reformatted. In this case, you can prevent areas of code being formatted with the Disable formatting for selected text from the actions list, which adds comments before and after the selected text to prevent that block from being formatted.

To change the way that formatting is done, there is the Edit Formatting Styles... window, accessible from the Prompt menu that displays the Formatting styles dialog box. You can change the current style at any time from the Active Style menu item, accessible from the Context menu or Prompt menu. This displays a submenu that allows you to select a formatting style from any current styles.

The Unformat item, found in the Actions list, will remove all formatting whitespace from the selected code. Apply Unformat to Listing 2, and you'll end up with code that looks as shown in Listing 3.

CREATE TABLE Pilot_Skills ( Pilot_Name CHAR(15) NOT NULL, Plane_Name CHAR(15) NOT NULL, PRIMARY KEY ( Pilot_Name, Plane_Name ) ); CREATE TABLE Hangar ( Plane_Name CHAR(15) NOT NULL PRIMARY KEY ); SELECT DISTINCT Pilot_Name FROM Pilot_Skills AS ps1 WHERE NOT EXISTS ( SELECT Plane_Name FROM dbo.Hangar WHERE NOT EXISTS ( SELECT Pilot_Name, Plane_Name FROM Pilot_Skills AS ps2 WHERE(Pilot_Name = Pilot_Name) AND (Plane_Name = Hangar.Plane_Name) ) );

You'll notice that it is not quite the same, because some actions were included with the formatting and these have changed the code slightly.

Refactoring Redoubt

SQL Prompt supports refactoring SQL objects and code in several ways. It can perform 'heavyweight' refactoring processes, such as renaming objects or splitting tables, which require a separate window or wizard to accomplish. It will also apply simpler, less-intrusive refactoring 'actions', right within the query window, such as removing unnecessary angle-brackets in identifiers or adding semicolons.

While SQL Prompt will run certain refactoring actions, by default, as part of the Format SQL command, these changes extend beyond the scope of 'formatting', since they change the code, irreversibly.

Refactoring Processes

Each of the refactoring processes requires a separate window or wizard to accomplish.

Smart Rename

With an object selected in SSMS Object explorer, SQL Prompt's smart renaming wizard can be kicked off from the Prompt menu, or from the right-click context menu, to open up its synonymous dialog box.

The Smart Rename wizard produces a script that include the T-SQL to rename the object and to modify the objects that reference the renamed object and allows you to review the script before executing it. Modifications will be made in the correct order to maintain database integrity.

Split Table

With a table selected in SSMS Object explorer, SQL Prompt's table splitting wizard can be kicked off from the Prompt menu, or from the right-click context menu, to display its synonymous dialog box.

The Split Table wizard aims to produce a script that creates a linked table, modifies the primary table, and modifies any objects that reference the primary table. Having checked the script, you can execute it. SQL Prompt wraps all the changes within a transaction, so they can be rolled back in case anything goes wrong.

A primary key on the secondary table will be created from the copied columns. Data is preserved, but duplicated data is removed from the secondary table.

Encapsulate as (New) Stored Procedure

With SQL text selected within a query window, the Encapsulate as (New) Stored Procedure... window can be reached from the Actions List, Prompt menu, or keyboard (Ctrl+B,Ctrl+E). It shows the dialog box that allows you to encapsulate the selected text as a stored procedure with any variables added as parameters.

Instant Refactoring within the Query Window

There are two quick refactoring tasks that be applied directly within the query window, using the context menu or from keystrokes.

Rename Alias/Variable

This refactoring will rename an alias or variable within a block of code. If the cursor is positioned on an alias or variable, and you select Rename Alias/Variable from the context menu or press F2, you can then tap in the new name and all occurrences in the query window are changed.

Inline EXEC

The Inline EXEC refactoring will allow you to replace the evocation of a stored procedure or scalar function with the code from within it. If you highlight any stored procedure or scalar function, in a query window, and then click on the Inline EXEC menu item in the same context menu, or hit Ctrl+B,Ctrl+F, it replaces this with the inline code.

Refactoring Actions

Actions are simple refactoring rules that we can choose to run as part of the Format SQL command. You can opt whether to add or remove square brackets around identifiers, change the casing (uppercase, lowercase, capitalized, or camel case) of identifiers, insert semicolons, expand wildcards or qualify object names. You can also decide whether the reformatting should include applying whatever column alias style you select.

From the Prompt menu, select Options, and navigate to the Format | Styles section, and you can select which actions will run automatically, as part of Format SQL.

Most of the Actions can also be applied independently except adding/removing square brackets and adding/removing the AS keyword on alias definitions for tables and views.

Apply casing options

As part of your currently-selected formatting style, you can define how to handle the casing of the built-in keywords, functions and types. The casing options are accessible from the Formatting styles window and are stored as part of the style.

You can leave the casing settings as is, or choose to apply lowercase, UPPERCASE, UpperCamelCase or lowerCamelCase. The casing options for the current active style will be applied to code as you type it in.

You can apply these setting to selected text via the Apply casing options menu item in either the Actions List, Prompt menu, or from the keyboard shortcut (+B,Ctrl+U). They will also be applied every time you run Format SQL, assuming this action remains activated in the Prompt Options menu.

Adding or removing inline Comments

From the Actions list, you can Add Comments to the selected text, meaning that the selected text will have the 'double- dash' line comment added at the start of each line. You can also remove the comment entirely with the Remove Comments action or just the -- inline comment token, with Uncomment action. Currently, /* */ comment delimiters cannot be added or removed by SQL Prompt.

Adding or removing the optional AS keyword

The AS keyword that is part of the alias definition of tables and views is optional, although it is better to use them because they make the text more readable. SQL Prompt allows you to add them or remove them. Both the Add AS Keyword and Remove AS keyword operations can only be done when reformatting, and your preference must be set in the Prompt Options menu.

Adding or removing square brackets to or from identifiers

In SQL Server, identifiers are supposed to have just Latin characters from Unicode Standard 3.2, from A through to Z, from a through to z, an underscore, hyphen, underscore (_), at sign (@), or number sign (#) and also some letter characters from other languages, along with decimal numbers from either Basic Latin or other national scripts. If you begin with a (@), or number sign (#) this will have special meanings. You must not use any of the T-SQL reserved words.

To allow the import of databases from systems with more relaxed rules, SQL Server allows identifiers to be delimited via Square brackets. You can Add Square Brackets to your identifiers or Remove Square Brackets, as part of a reformatting.

If the Add/Remove square brackets refactoring action is not configured to run as part of Format SQL, which it isn't by default, then only Remove Square brackets is available as an independent action. This will switch to Add Square brackets if we activate the Add brackets to all identifiers setting. This will remain in place until either we restore the default settings, or explicitly activate Remove unnecessary square brackets.

Qualifying object names

SQL Queries will run slightly faster if object names are qualified in a dotted notation that specifies ancestry. This means that you should specify the schema of a database object, where appropriate, and provide the table source for a column, schema for table and so on.

The Qualify Object Names action adds the extra information required to identify the object's parentage. It can be run during reformatting, or is accessible independently from the Actions List or Prompt menu, or via the keyboard (Ctrl+B,Ctrl+Q).

Expand Wildcards

To avoid the problems that come from maintaining code that has the SELECT * FROM <Table-source>, you can select the code and use the Expand wildcards action. It will replace the Select * wildcard in the query with the entire column list of the table source.

It can be run during reformatting, or is accessible independently from the Actions List or Prompt menu, or via the keyboard (Ctrl+B,Ctrl+W).

Insert Semicolons

This will insert semicolons at the end of every statement, if they have been omitted. They are optional in the SQL standard but make it easier to read a batch. They can become a nuisance if you are appending clauses to an existing SQL query. The Insert semicolons action can be run during reformatting, or can be reached from the Actions List or Prompt menu, or done via the keyboard (Ctrl+B,Ctrl+C). There is no way of removing semicolons that I've found. That treasure remains undiscovered.

Converting Query Results into Code

SQL Prompt's right-click context menu, used within an SSMS Results pane, reveals two features that will convert query results into code: Copy as IN clause and Script as INSERT. A third results grid feature, Open in Excel, simply exports the results to Excel.

Copy as IN Clause

If you select a single column, or one or more values in a single column, you can use this feature to format the selected values into as list use in the IN clause of query.

Script as Insert

When you select the entire grid, a single cell or block of cells, or adjacent values in a single column, you can use this feature to converts the selected values into a multi-row INSERT INTO...VALUES expression.

The Sands of Suggestion

SQL Prompt offers as-you-type IntelliSense and code completion suggestions, as well as providing other information, and helping you navigate around your code.

There are two boxes that pop up when you are typing in code. One of these is the object definition box, and the other is the suggestion box.

The Suggestion Box

The location-sensitive suggestion box is shown after a set period, which you can specify from the Suggestions | Behavior tab of the Prompt Options menu. It can also be switched on and off entirely from the same tab by toggling the Show Code suggestions check box. Once off, the suggestions box will not be shown until you select the check box again, or choose Enable Suggestions from the SQL Prompt menu, or use the (Ctrl+Shift+P) shortcut.

If the suggestions don't seem to reflect the code changes you've been making during the session, you can click on Refresh Suggestions or hit the (Ctrl+Shift+D) keys to update the contents of the suggestion box to reflect the cursor position.

To insert the currently selected suggestion into your code, you hit an insertion key. The default insertion keys are ENTER and TAB but you can choose an alternative insertion key from the Prompt menu.

The Object Definition Box

When you select a suggestion, the object definition box is shown, containing information about the selected object.

By default, for tables and views, a summary is shown, listing the column names and data types, but you can see the object creation script instead by clicking the Script tab of the object definition box.

If you don't want the object definition box to be displayed automatically, clear the Display object definitions checkbox in the Prompt | behavior tab of the Prompt Options menu.

You can view the object definition for a specific object in your query by clicking its tooltip.

Finding Dependencies

The Dependencies tooltip lists all the objects referencing (using) the column and being referenced by the column.

To get this useful information you need to select the column and move your mouse over the selection so that the tooltip appears

Then click the link in the tooltip.

Scripting the object

You can script out an existing function or procedure as an ALTER DLL statement by using the Context menu or Prompt menu item Script Object as ALTER, or use the keyboard (F12). If the name of a function or procedure is selected, this creates a new query window with the script of the routine so you can alter it.

Finding Invalid objects

The Find Invalid Objects in the Object Explorer's context menu, or the Prompt menu opens a new browser pane with a list of all objects that reference non-existent objects in the database.

Finding Unused Variables and Parameters

The Find Unused Variables and Parameters Prompt menu or keyboard (Ctrl+B,Ctrl+F) finds unused variables and parameters, and underlines the variable with a tooltip-based error message on mouse-over.

The Tab Management Terraces

SQL Prompt keeps a history of the query windows (tabs) that you open in SSMS. This is a great way of tracking down scripts that you forgot to save, or that you were working on when the application crashed. With tab management, it is difficult to lose work.

Browsing tab history

To browse your tab history, on the Management Studio toolbar, click Tab History:

The tab history window is displayed:

The tab history window shows the file name, the SQL code, the SQL Server, and the database, to which the tab was connected, and the time the tab was closed.

To view a list of tabs that are currently open, click the middle Open tab in the tab history window: You can navigate between the tabs with the keyboard. To browse open tabs, press (Ctrl + Q).

To navigate the tab history window via the keyboard, you can press the Up or Down arrows to move through the tab list. You can then press Enter to open the selected tab, and to switch between the All tabs and Open tabs lists, you can press (Ctrl + Left arrow) or (Ctrl + Right arrow).

You can rename a closed tab that still has its meaningless SQLQuery*** name, by right-clicking the tab within the tab list and choosing the Rename menu item. You can then type a new name for the tab, and press Enter.

You can also remove tabs, either singly or as a set, from the history in a similar way. To remove a single tab, in the tab list, right-click the tab you want to remove and click Remove. If you want to remove all the old tables older than the one selected, click Remove tabs older than this:

When you start Management Studio, SQL Prompt automatically restores open tabs from your previous session. You can change what prompt does from the Tabs > history pane of the Prompt Options menu.

Tab colouring

Tab Coloring colors the tabs of each query window, based on the server you're logged into. It is coloured according to the type of database environment that server is hosting. Unlike the status bar at the base of each query window, which tells you which server and database your query window is logged into, the primary colour around the border is hard to miss.

If you are a busy DBA, this can be a godsend because it is only too easy to make an important change in the wrong window. SQL Prompt colours the tab of the query window and adds a line of the same color below the status bar. If you prefer to work with undocked query windows, the whole window is outlined in the tab color, so it is hard to miss!

Code Snippet Cove

Code snippets provide a way of doing routine tasks in SQL, without having to rely on remembering the syntax of all the types of SQL Statements and queries. They also save a lot of the chore of typing.

Any developer will be familiar with the idea of templates and snippets, which basically allow you to reuse 'chunks' of code, such as function definitions.

SQL Server has Templates and, more recently, snippets that help with this. SQL Prompt has snippets. These SQL Prompt snippets are compatible with the SSMS Templates in that you can paste your favourite SSMS Templates into the Prompt Snippet Manager window and it will still work. You'd probably want to alter them because snippets add some powerful magic called Placeholders.

You can specify the cursor position, indicating where the snippet is inserted ($CURSOR$). You can insert the current date ($DATE$), the name of the database ($DBNAME$) or Server ($SERVER$), a globally unique identifier ($GUID$), the name of your PC($MACHINE$) or user ($USER$), the clipboard contents ($PASTE$), or the currently selected text ($SELECTEDTEXT$). By combining the $PASTE$ and $SELECTEDTEXT$ placeholders in a snippet, you can combine two sections of code into one completed snippet.

There is a way of selecting part of what is inserted into the query window by the snippet, using $SELECTIONSTART$ and $SELECTIONEND$. This is handy if you would like part of the code already selected after you have selected your snippet from the Action List. You may, for example, want to copy part or all the completed snippet to other places within your code, and by selecting it, you save routine work.

You can add your own placeholders, which will be very useful if you are sharing snippets across a team because it allows you to personalize them. The following screenshot shows part of a snippet that I created for my own use.

The Caves of Code Analysis

Code analysis is a new feature that aims to check for all the aspects of your coding that could be a problem.

You get polite green wavy lines under any part of the script that could do with improvement. The code analysis tool checks for all the common issues with code that are generally accepted, and searches your code for them. These are generally only advisory as only a few will prevent your code executing. Some of these problems will drain the database performance, others increase technical debt, and a few will cause problems to anyone subsequently having to maintain the system. A few of the issues could potentially create a potential security loophole.

Here is a simple example of a minor, but important, issue that needs fixing when you are coding to production standards.

In this example, I've specified a table in a query without specifying the schema for the table. If I'm in DBO schema, for example, I should make that explicit. After all, in the future some of the users of the system may be assigned to a different schema.

Not only is it a good habit to get into, but it makes queries run faster. There are a whole range of 'SQL code smells' for which SQL Prompt will check, over ninety at the moment and rising to around two hundred. If you click on the link, you get a short explanation of the SQL Code Smell, and this will link to a longer explanation.

This feature is very valuable for the busy database developer because it will alert you to things that may not affect the immediate problem of getting your code to execute, but will affect the eventual usefulness of your code, and are much more easily dealt with immediately rather than causing technical debt to mount up.