Introduction

During the early days of SQL Server 2005 beta, there was talk about including IntelliSense® into the new SQL Server development environment. Unfortunately for the fans of IntelliSense®, this feature either never happened in the first place or was removed in later versions. Most developers either love or hate IntelliSense® – some see it as an intrusion and others as an invaluable aid.

Fortunately for the latter group, Red-Gate have provided SQL Prompt, an IntelliSense® style word completion for Microsoft® SQL Server editors. This is available from the Red-Gate site as a free download until 1st September 2006, promising “no time-bombs and no restrictions”.

What is IntelliSense®?

IntelliSense® is a word completion feature typically provided in the Microsoft Visual Studio platform. In the VB6 world (with which I am most familiar), a developer enters an object name, presses the full stop and is prompted with a list of methods or properties owned by the object. This is particularly handy if the developer is not familiar with the object in question. There are other ways to use IntelliSense® in VB and .NET, but these are outside the scope of this article.

SQL Prompt by Red-Gate

I’m going to save time and quote the comments on the download page for this product:

“SQL Prompt™

Intellisense for SQL Server, plus other features

Code completion for fast, accurate script building

Discoverability in SQL Server query creation

Keyword formatting, code snippet integration other extended features

FREE until 1st September 2006

No time-bombs, no restrictions

SQL Prompt provides Intellisense® style auto-completion for Microsoft SQL Server editors. It will tell you the exact formatting you should use for your SQL commands, while you are writing them and will therefore help you write fast, perfectly formatted SQL statements. SQL Prompt improves the productivity of all SQL script creation. SQL Prompt can be downloaded free until 1st September 2006 and we are providing forum support on the SQL Prompt support forum. A web help file is also available.

SQL Prompt simply sits behind the scenes and provides unobtrusive help when you press Ctrl-Space or when you type "." after a table/view/alias name.

Features include:

Table/View name completion

Column name completion

Stored procedure name completion

USE completion

JOIN/JOIN ON completion

Auto-uppercasing of keywords

Auto-popup after keywords

SQL Prompt works with Microsoft Query Analyzer, SQL Server 2005 Management Studio, Visual Studio 2005, Visual Studio .NET 2003, SQL Server 2000 Enterprise Manager, UltraEdit32. Please note that Intellisense is a registered trademark of the Microsoft Corporation, we have a dedicated web page describing Red Gate's relationship with Microsoft.”

Here is the best part: $free

Don’t you love seeing that last word, and coming from a company with the reputation of Red-Gate, you can expect a good product.

Using SQL Prompt

After downloading and installing the product, you will notice a new icon in your taskbar. SQL Prompt can load when you start Windows, and it constantly monitors your keystrokes. Double-clicking on the icon opens the Status/Options/About dialog, but we will get to that later.

I enjoy using SQL2K5 Management Studio, but this tool with work with SQL2K’s Query Analyser. Open a new query window and enter ssf followed by the TAB key. The text SELECT * FROM is automatically entered, and you are presented with a candidate list of all tables on the current database (you will need to wait a bit at first until SQL Prompt retrieves the current database's metadata). Start typing part of the name of the table you want, and the selection will move down to the first entry matching what you have typed. Move the cursor, and you can select the table you wish to read from. Press full stop and you will see a list of all fields on that table, in alphabetical order.

I’m sure by now you can see the benefits of this tool if you are unfamiliar with the database schema. If you do not wish to use the prompt, press CTRL or ESC and the candidate selection will disappear. You are not forced to use SQL Prompt when writing your queries; it is an optional aid.

If you wish to see a list of all tables in the database while in the query window, it is possible to invoke SQL Prompt by pressing CTRL + SPACEBAR.

Customizing the tool

I am sure many of you will agree the ability to customize a tool is high on the list of wants in a product. SQL Prompt provides just that. Switching SQL Prompt off is easy – open Options (from the taskbar) and select ‘Off’.

SQL Prompt creates a new connection for each database where you have used the tool. Clicking 'Close All Current Connections' will result in SQL Prompt closing the connections it owns, and releasing cached metadata. This need to create connections could be tricky when a DBA needs to restore a database, as the restore will not work if any connections are open to the database. The fact that SQL Prompt opens a connection to each database raises red flags in my mind, so I don't think I would run this against my production databases - each connection consumes system resources, and need to be kept under control. Your DBA may wish to create a nightly job to drop these connections; alternatively logging off your PC at the end of the day will have the same result.

Additionally, under the Options tab you can customize a trigger word for example, EXEC, ON, JOIN. Triggers are words which result in the candidate list being displayed. For example, enter the trigger word EXEC, and a candidate list of all stored procedures in the current database will be displayed.

Other useful customizable features allow you to control in which editors SQL Prompt will be enabled, and in the case of Visual Studio.NET – which file extensions to support (.sql and .prc).

Snippets (shortcuts) can save you time by inserting pre-defined code. By default, ssf (SELECT * FROM) and w2 (sp_who2) are included by default. Type w2 and press TAB, and sp_who2 is entered and executed. You are able to add your own snippets, which (I suspect) will make this tool immensely popular to those readers who enjoy using shortcuts.

Automatic Capitalization is a feature I particularly enjoy, but if you do not like it – turn it off or change the list of words which will be capitalized.

Moving further into customizing the tool, the user is able to choose which items will appear in the candidate list, any databases to ignore and other options such as font and prefixing with owner/schema.

Conclusion

Although IntelliSense® takes getting used to (if you have not used the feature before), I feel this tool will be particularly useful to developers who are not familiar with the database schema, and those who enjoy working with shortcuts. Even if you do not fall into the above categories, I am sure you will enjoy using SQL Prompt™. Get it now before 1 September 2006!