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.
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.
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.
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:
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 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;
Another example ‘sg’, creates a common Select statement as show below;
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.
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.
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.
» See All Articles by Columnist Don Schlichting
Subscribe to Cloud Insider for top news, trends & analysis
DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases–foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.
Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.
Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved
Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.
Leave a Reply