Introduction
SQL Assistant is an intellisense tool designed to increase DBA coding accuracy and productivity. It does this by injecting code snippets, automatic word completion, and attribute browsing into the SQL coding window. In addition, the product also includes syntax validation and code formatters. SQL Assistant supports MS SQL, Oracle, DB2, and MySQL. There is a long list of supported targets including Microsoft SQL Query Analyzer, MS SQL Management Studio, and the Oracle SQL Plus editor to name just a few. The product itself is created by Soft Tree Technologies, who specialize in high tech software. This article will explore some of the features and benefits of SQL Assistant.
Installation
To start, a trail version of SQL Assistant 2.0 can be download from the Soft Tree site at the following URL; http://www.softtreetech.com/index.htm . The hardware requirements are minimal and include an XP, Win2k, Win2003, or Vista computer with an extra 3 MB of disk space. Once downloaded, double click the executable and follow the prompts. Installation takes only a few minutes. Once installed, there will be a new small SQL icon in the task bar indicating SQL Assistant is running. The memory footprint for SQL Assistant is small, a little over 4 MB. In addition to the application, the installation will also create a program group with a user guide. The examples in this article were created in the SQL 2005 Management Studio. However most popular databases are supported including Oracle 8i, 9i, 10g, Microsoft SQL Server 2000, 2005, DB2 UDB 7, 8, 9, and MySQL 5.
Code Completion
Probably one of the most recognizable features of SQL Assistant is object name code completion. This feature is enabled by default and will cause a context sensitive popup to appear while coding, helping to finish statements for you. In the example below, the Adventure Works database was being queried. After typing “FROM”, the popup below appeared showing a list of object choices.
Code Completion will return several different object types including Tables, Views, Databases, Schemas, Functions, Procedures, and Table Functions. Using the above example, if the first character of the target is typed, the popup list will limit itself to only return possible matches as shown below.
Another way code completion can be invoked is by using the hot key Ctrl + Space while typing. SQL Assistant is very hot key friendly. As an example, press the hot key while typing “SELECT * FROM per”. Because there is only one object that starts with “per”, SQL Assistant will auto-complete the statement to “SELECT * FROM Person”. Had there been more than one choice, a popup showing the options would have appeared. Another feature of the SQL Assistant popup is the ability to walk down the tree list of opions and select the final object. For example, using the same code “SELECT * FROM p”, the popup tree can be expanded all the way to the final column. Doing so will compelete the statement to “SELECT * FROM Person.Address.City” as shown below.
Code Formatting
SQL Assistant is configured to help with code formatting by default. There are a couple of features that assist with this task, Keyword Capitalization being one of them. SQL Assistant comes preloaded with a list of keywords such as “alter”, “select”, “array”, and “as”. When these key words are typed into a code window, SQL Assistant automatically converts them to upper case. The key word list is fully configurable and can be accessed by right clicking the SQL icon in the task bar, then selecting Options, Code Formatting.
Another very hand code formatting feature is Smart Auto-Indent. This feature allows you to create a template for key procedures, such as CASE. The template for a CASE command is shown below:
CASE …
WHEN … THEN …
ELSE …
END
When entering a CASE statement into a code window, the text will be auto formatted to fit this pattern. In addition to the preloaded procedures, custom formatting rules can be easily added.
Code Snippets
Code Snippets are handy shortcut aliases for blocks of text. For example, in the code window, typing “ife” then the hot key combination Ctrl + Enter will produce the following “IF” “Else” statement;
IF
BEGIN
END
ELSE
BEGIN
END
Another example ‘sg’, creates a common Select statement as show below;
SELECT
FROM
WHERE
GROUP BY
ORDER BY
Code Snippets can be espically helpful for headers on stored procedures or views where a description with the date created and author are required. This is because SQL Assistant includes the ability to use Macro variables inside Code Snippets. For example, the macro variable $DATE$ can be used inside a snippit and when run, will substitute todays date. An example included in the documentation shows a snippet called “fun”. When invoked, $DATE$, $OSUSERS$, and $LOGINS$ are resovled to current values.
CREATE OR REPLACE FUNCTION |
(
v_in IN <data type>
)
RETURN <data type>
/***********************************************************
* Function description:
* Date: $DATE$
* Author: $OSUSER$ connected as $LOGIN$
*
* Changes
* Date Modified By Comments
************************************************************
*
************************************************************/
IS
DECLARE
v_ret <data type>;
BEGIN
v_ret := …;
RETURN v_ret;
END;
Reference System
The SQL Assistant Reference Systems allows you to build SQL commands interactively with lookups. The system is invoked while coding by the hot key combination Ctrl + F1. Two windows encompass the Reference System, the “Table of Contents” window, and the visual SQL Command Builder window, both are shown below.
The Reference System can be used to locate syntax and statement structures. Entire statements can be built interactively.
Conclusion
SQL Assistant can help increase DBA productivity, and simple code development. To recap, the main productivity features included Code Compeletion, Code Formating, Code Snippets, and a Visual Reference System. The Assistant is easy to customize, and personal code snippits and shortcuts are simple to create. Hot keys make the product easy to invoke while coding. There is also support forum run by Soft Tree. A new version, 2.5 should be available soon.
