My Favorite SQL Prompt Features

SQL Prompt from Redgate Software is one of the tools that I cannot live without. Well, that’s probably an exaggeration but I use it daily in my job. Whether I’m writing stored procedures or crunching TSQL codes to troubleshoot data issues in SQL Server, I depend on it to show me the things that I need – and even the things that I don’t think I need. It’s the IntelliSense everyone needs for productivity.

The features and code-complete capabilities of SQL Prompt makes a productive SQL Developer or DBA more efficient. I think I have told my co-workers more often than I should that I couldn’t write TSQL without it. In fact, I wonder how anyone could do their job productively and efficiently without it or any other type of IntelliSense tool for that matter. SQL Prompt is just the best code completion and refactoring tool on the market.

A little disclaimer, though. I am part of the Friends of Redgate Program (FoRG). I have long been a fan of SQL Prompt even before I became part of FoRG and, I think, even before Redgate acquired the tool (If my memory serves me right, it was not an original product of Redgate, or maybe that was something else).

I like all the features of SQL Prompt, but these are the few that I like most.

Code Analysis

The Code Analysis feature guides you to writing better codes and avoiding common pitfalls of code smell. This is how it works. As you type, the tool scans your code against a set of rules and mark the lines that violate these rules. An explanation of each rule is provided and also some suggestions on how to improve your code. Most important of these, in my opinion, are the Deprecated Rules that warn you of, well, deprecated objects.

As the analysis is performed in real-time, you can correct your codes as you write. As of this writing, you cannot create custom rules but the existing ones are good enough to ensure that you are writing efficient codes. Other rules are Best Practice, Execution, Performace, and many others.

Snippets Template Parameter

You probably have a set of scripts that you often run, like troubleshooting scripts or query. My favorite feature is using snippets with template parameters. Say, you have a script that has a variable. Normally, you would hardcode the variable or leave it empty when writing the snippet. When invoked, the snippets allows you to specify the value of the parameter.

Here’s an example. Create a snippet and add the parameter placeholder with format <Parameter, Type, Value>.

SELECT ProductName, ProductDescription
FROM Product
WHERE Price > <price, smallmoney,100.00>

When the snippet is invoked, a form pops up:

Table Alias

This is a good way to be consistent with table aliasing. Consistent table aliases across the company is also a best practice to maintain. You can set object aliases in the options menu.

Encapsulate as New Stored Procedure

So you’ve written a long TSQL code and decided to convert it to a stored procedure. You can do that on the fly by using the Encapsulate as New Stored Procedure wizard. Here’s an example of the generated script.

/*
Script created by SQL Prompt version 9.0.7.3692 from Red Gate Software Ltd at 1/20/2018 10:57:28 PM
Run this script to create the encapsulated stored procedure.

Please back up your database before running this script.
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.ProductPrice AS
BEGIN
SELECT ProductName, ProductDescription
FROM dbo.Product 
WHERE Price > 200.00
END

GO

Custom Style

You can use Custom Styles to enforce Coding Conventions. You can create styles from scratch or use one of the existing styles.

You have an option to preview your current query with the style selected.

SQL Prompt is not only a productivity tool. You can also use it to enforce Best Practices and conventions within your organization. What is your favorite feature? Share them in the comment below.