Well, it's not exactly the Holy Grail of SQL Server utilities, but it's close. Imagine having the convenience of IntelliSenseTM from within SQL Server Management Studio. That's what you get with Red-Gate Software's new utility, SQL Prompt.
Back in January, I reviewed SQL Refactor, also a Red-Gate product, of which I am very fond. Shortly after that, I was informed of the impending release of SQL Prompt and was very excited to get my hands on it. Here's why:
With SQL Prompt, you can ...
- insert table names from list of those available
- insert column(s) from list of those available
- apply keyword formatting while you type
- view and copy schema info & DDL scripts
- create & manage your helpful code snippets
SQL Prompt, once installed, runs as a service and sports an icon in the system tray to declare its presence and to provide a convenient management interface. There is also a menu that displays in SQL Server Management Studio, but this is only necessary for setting options and saving snippets. The bulk of the work is done automatically as you type, just as it does with IntelliSense in a VBA or Visual Basic module. As with its VBA counterpart, SQL Prompt works as you type.
Before we get started exploring the actual working function of this utility, we should mention the only menu selection of interest: the Options page.
Set Your Options
If I start describing every available option, this article will get real boring, real fast, but allow me to briefly laud this product's flexibility. Unlike the auto-complete function in Microsoft Access or Visual Basic, SQL Prompt allows you to change the way it behaves. This is very helpful, because some things that the designers of this tool thought were the greatest thing since toast, became a source of irritation to me and I was happy to be able to turn them off.
For example, the option for "Completion Keys" is set to Enter by default, but I'm used to working in VBA where the Tab key will effect the auto-complete. This is no problem for SQL Prompt, as you can choose from a number of keys ... even multiple keys. Another option I find clumsy is Automatic Closing Characters. It allows you to type one character, like the left parenthesis, and SQL Prompt supplies the right (closing) parenthesis. Same for single and double quotations, square brackets and comment marks.
The options page above doesn't contain the feature I just described, but it is typical of the attributes and layout available for all user customization options. As an aside, the Help file is especially useful. As a rule, I hate reading Help files, but I made an exception in this case and actually figured out some cool features that were not intuitively obvious to me. The most important one for me was the Snippets page.
Prior to using SQL Prompt, my Snippets were handled as custom SQL Templates. This works well enough, but is not terribly convenient. With SQL Prompt snippets, I can create new snippets from the options interface shown above, name it appropriately, and call them out with the auto-complete function. When you type the snippet keyword (Epicor in my example above), this utility prompts you to insert your snippet text. I love this thing!
Those of us who have worked with VBA or VB code editors already know how it works. As the system begins to recognize what you are typing, it suggests words (or phrases). In the example shown below, I am adding several new fields to the select statement all at once by clicking the appropriate check-boxes. (IntelliSense in VBA never even tried to do that!)
Another handy-dandy feature reveals itself when you select from available objects that may be scripted, like tables, views and stored procedures. An additional SQL Prompt dialog window appears that includes the text required to create the object, along with a button that simplifies copying the script to the clipboard.
Will I ever use this feature? I don't know for sure, but I'm glad it's there. Fact is, most of these features are in SQL Prompt because developers asked for them. Some will be more useful than others will and the feature list is certain to grow. Though this product carries a Version 3 moniker, it's really a complete rewrite of the old Version 2 product that Red-Gate acquired. They went to great pains to clean it up and expand its usefulness and in my opinion, they succeeded in creating a great product.
Is SQL Prompt For You?
If you're still not sold on it, check out the on line demo. The cost for SQL Prompt is $195 per user, which might place it in the "nice to have but ..." category, but I'm convinced that this tool makes me considerably more efficient. With as much time as I'm spending in SQL Server Management Studio these days, it will pay for itself in no time.