As a consultant I have only a limited amount of time to work on any given project, which means I need to be as efficient as possible and I am constantly on the lookout for the best time saving tips, tricks and techniques to increase productivity. Enter: Redgate SQL Prompt. Without question, SQL Prompt has been the number one tool in my toolbelt for increasing productivity when working in SQL Server Management Studio and it has allowed me to save precious resource hours developing that I’ve been able to then devote elsewhere.
This blog will cover some of my favorite features of SQL Prompt, along with examples from the AdventureWorks2014 database.
Note: All screenshots and examples use SSMS v 17.8.1 and SQL Prompt v 9.4
Formatting and Re-Factoring through the Action List
Formatting and re-factoring code can be tedious and time-consuming, but SQL Prompt makes the job much easier with a menu of options to automate the process. To view the options available for re-formatting, highlight the section of code you would like to re-format then click on the icon that appears to left of the code to open the Action List. Once selected, the menu will pop up with a list of options that can be selected and applied. Although there are 20+ options in the menu, I will highlight just a few of my favorites below, along with their keyboard shortcuts.
Figure 1 Formatting and Re-Factoring Menu
Expand Wildcards (CTRL+B, CTRL+W)
This option will expand any asterisks(*) in your code into a list of the columns they represent. Additionally, if you have assigned an alias to a table, any columns from that table that are expanded as a part of the ‘Expand Wildcards’ features will include the alias qualification. Below is an example of this feature being applied. The code on the left represents the original SQL statement, and the code on the right is the statement after the feature has been applied (Note: if you would like to qualify the columns regardless of whether or not the table has been aliased, that setting can be adjusted in the Options menu). Notice the table aliases have been used to qualify each column name.
Figure 2 Before and After ‘Expand Wildcards’
Format SQL (CTRL+K, CTRL+Y)
This option will re-format your SQL code to whatever style is selected in the formatting style section of the SQL Prompt Options menu. You can select from one of the default options provided or create your own formatting preference. By re-formatting code it becomes much easier to read and allows for consistency between all code that is written. Below is an example of a code snippet before and after having formatting applied. In the ‘After’ example, notice the table ‘AddressType’ is now qualified with the schema name, the columns coming from that table are qualified with the table alias, and lines have been separated and indented for readability.
Figure 3 Before and After ‘Format SQL’
Add Quotes & Commas
This option will add single quotation marks and commas around each item in a highlighted list. The benefit of this feature is clear when working with datasets from an outside source such as Excel, where you may be copying and pasting a list of values to an IN clause. The following is an example of before and after this functionality has been applied.
Figure 4 Before and After ‘Add Quotes and Commas’
Keyboard Shortcuts for Quick Updates
In addition to the options available in the Action List, SQL Prompt offers other shortcuts to help increase efficiency during development. Below are a few of the shortcuts that I find to be quite helpful.
Script Object as ALTER (F12)
When the cursor is over an object, such as a table, view or stored procedure, hitting the F12 key will script the object into an ALTER statement. This allows you to look at the underlying code and make adjustments as necessary.
Select in Object Explorer (Shift+F12)
From time to time it is helpful to look at an object in Object Explorer, and the shortcut of Shift+F12 allows you to do that quickly. Similar to the previous shortcut, this will be applied to the object that is actively selected by the cursor.
Execute Current Statement (Shift+F5)
When you hit the F5 key in SSMS it will either execute all SQL statements in the query window, or only those which have been fully highlighted. SQL Prompt offers an alternative to this through the shortcut of Shift+F5, which will execute only the statement where the cursor is placed, and it does not require the entire statement be highlighted.
Rename Variables and Aliases (F2)
Sometimes it may be necessary to rename existing variables and aliases, and a shortcut exists to help with this rename within a segment of code. To utilize this function, select an instance of the alias or variable in your code and hit the F2 key. This will highlight the alias and any changes made to the highlighted instance will be applied to all others in the code.
Result Grid Actions
Beyond working in the query window, SQL Prompt also offers a set of features for working with the result grid. To access these features, highlight the section of the result grid you would like to work with, right click, and select the option you would like to apply. An example of the options available are circled in red in the image below.
Figure 5 Result Grid Options Menu
Script as INSERT
Selecting this option will generate a script that creates a temporary table, inserts the selected records into it, and drops the temporary table. Once the script has been generated you can modify and update it as necessary for your needs. An example of this feature, utilizing the first five rows of the result set shown in Figure 5, can be seen below.
Figure 6 ‘Script As Insert’ applied to results in Figure 5
Copy as IN clause
This option will copy the results you have selected into the format of an IN clause and save to your clipboard so you can paste it into a query. This function will also eliminate any duplicates, so your IN clause will have only one instance of each attribute selected. For example, if this option was applied to the highlighted section of the result set shown in Figure 5, it would return the results you see below.
Figure 7 ‘Copy as IN Clause’ applied to results in Figure 5
Open in Excel
This option is fairly straightforward and functions exactly as it sounds – it will open Excel and load the results you have selected into a spreadsheet. This feature will automatically copy any column headers and it will maintain formatting so that no leading zeroes are dropped from your results.
Another great time-saving feature of SQL Prompt is Smart Rename, which allows you to rename objects without breaking dependencies. To use the feature you must locate the object you wish to update in Object Explorer, right click, and select ‘Smart Rename’ from the drop down menu. When this feature is selected it will open a wizard that allows you to specify a new name for the object, see the actions that will be taken, view any potential warnings that should be considered prior to making the update, and generate a script that will modify your original object as well as any that have dependencies on it. An example of the Wizard, being utilized to change the table Person.Person to Person.Persons can be seen below.
Figure 8 Smart Rename Wizard, showing Actions that will be taken by the script
Before using this feature, there are a few key items to keep in mind:
- The script that is generated will not only modify the object you specify, but will also modify any objects that it references in order to ensure dependencies are not broken
- If objects have previously been re-named using SSMS or the sp_rename command, the object definition will contain the original name and will not be included in the Action list or script
- Permissions and other properties of the object are preserved
Once you have selected ‘View Script’ from the wizard, the script will open in a new query window. The script will begin with a commented-out segment of code that provides a summary of the actions it will perform, followed by the script itself. It is always good practice to thoroughly review the script and make sure you have a firm understanding of the updates it will make prior to executing it.
The final feature I’ll cover is also the one I utilize most often – Snippets. Snippets are saved blocks of code that can be inserted into the query window quickly through keyboard shortcuts or menu options. SQL Prompt not only comes with a selection of default snippets, but it also lets you easily create your own through the Snippet Manager, which can be accessed through the SQL Prompt drop down on the main tool bar. To insert a snippet into the query window, simply enter the Snippet value then hit the Tab button, which will expand the Snippet into its underlying Code. Alternatively, once you begin entering a snippet a suggestion menu will pop up and you can select it from there as well.
Figure 9 Snippet Manager
SQL Prompt comes with a number of default snippets based on the queries and commands most often used by developers. Here are just a few of the default snippets I find most helpful on a regular basis:
|ssf||SELECT * FROM||To select all records from a table|
|ii||INSERT INTO||Will create an insert statement that lists out the columns in the associated table and required data types for any inserted values|
|ob||ORDER BY||To order results by a specified column|
|ap||ALTER PROCEDURE||Will open specified procedure in an ALTER statement to make updates|
|st100||SELECT TOP 100 * FROM||To select top 100 rows from a table for data profiling|
|cp||CREATE PROCEDURE||Will generate statement to create a stored procedure and includes parameters to update the schema and procedure name|
|gb||GROUP BY||To group results by specified attributes|
In addition to the default snippets included with SQL Prompt, you also have the ability to customize your own. To do this, open the Snippet Manager and select the ‘New…’ button. A window will open that will allow you to name the snippet, provide a description of its functionality, and enter the code it will call. Within the code you also have the ability to add placeholders or parameters. Placeholders will insert specified text when the snippet is called to action and are identified in snippet code by being enclosed in dollar signs. For example, the placeholder $DATE$ will insert the current date when the snippet is used. Parameters are specified in code with angle brackets and allow you to set the parameter name, as well as an optional data type and default value. For example, a parameter labelled ‘Name’ with a datatype of varchar(50) and a default value of ‘Ashley’ would be represented as <Name, Varchar(50), Ashley>. When a snippet is called from the query window a menu will open that will allow you to update any parameter values (this window can also be opened with the shortcut CTRL+SHIFT+M).
Below is an example of both the guide used to create a new snippet (Figure 10) and how that snippet actually appears in the query window (Figure 11). In this example, I have utilized two default placeholders as well as a customized parameter. I have set the parameter name to ‘CustomValue’ and have chosen not to specify a data type or default value.
Figure 10 Creation of new snippet in Snippet Manager
Figure 11 Snippet as it appears in query window
The convenient keyboard shortcuts and user friendly functionality of SQL Prompt have made it one of my most valuable time saving tools, but I do believe it’s important to only use those functions I have already mastered on my own, and I would recommend the same for any user. For example, you can use a default snippet to generate code for creating a stored procedure, but if that’s not a process you’ve done manually in the past then you won’t have a thorough understanding of how it actually works and you risk depriving yourself of that knowledge.
There are some features that come standard with Microsoft SQL Server that are similar in nature to those I’ve described here from SQL Prompt, but because I find their SQL Prompt alternatives easier to access and use, and their functionality more robust, I favor them in my own day-to-day work and have chosen to highlight them here.
If you have any feedback on this list, or tips of your own for increasing productivity within SQL Server Management Studio, I’d love to hear it! Please feel free to reach out to me at firstname.lastname@example.org.