Altova DatabaseSpy makes DBs user-friendly

Boasting some impressive features, easy-to-use query tool is a boon for non-DBAs

When discussions of the enterprise-data explosion get under way, the focus tends toward topics such as how to manage large amounts of data, how to keep it secure, and how to make it highly available. One subject that often gets overlooked is how the data deluge has affected the everyday work habits of the non-IT worker, or even the non-DBA: It's forcing ordinary end-users to get up to speed on the complexities of DBs and working with data.

Altova's DatabaseSpy 2007 does a pretty good job of lowering the learning curve for a lot of common database tasks so that employees can concentrate on their jobs instead of becoming DBAs. All in all, it's quite a nice tool that's quick and easy to set up and use. Although it lacks some administrative functionality that would be attractive to admins, it still boasts some surprisingly rich features, such as IntelliSense code-completion. It's safe to predict that Altova has some pretty big plans in store.

For my tests, I started off by installing DatabaseSpy on my new Vista machine. The setup was very easy with few choices to make. Most anyone should have no trouble at all getting DatabaseSpy running.

DatabaseSpy's editing features were the most impressive of the product's capabilities. For starters, there's the very nice IntelliSense code-completion feature that beats SQL Server Management Studio's hands down. (Oh that's right: SSMS doesn't have IntelliSense.) IntelliSense not only fills in table names and other objects as you type, but it also gives you reserved words. Notably, the features work only for SQL Server, although DatabaseSpy works with all databases.

Surprisingly, the IntelliSense gives you all of the DBCCs (database console commands), the internal procedures DBAs use to perform much of their admin functions such as re-indexing. This is pretty advanced functionality for what essentially equates to an end-user tool. If you ask me, it's proof positive that Altova has much bigger plans for this product.

Another really nice feature is the Favorites section in the tree view. By adding any objects in a project to your Favorites list, you no longer have to keep searching through hundreds of objects to find it; rather, you'll have easy access to it whenever you like. This, too, is lacking in competitors' native tools.

Other nice coding features are the ability to define named code regions, and to send query results to named result panes. Named code regions allow you to specify a section of code that you can collapse, making long code routines easier to sift through. Named result panes are tabbed query result windows that you can name in order to keep different result sets straight. It can be very handy.

Click for larger view.

DatabaseSpy also has very useful import/export functionality. It allows you to import data quite easily from flat files and export it to a number of destinations, including CSV, XLS, HTML, XML, XML Structure, and -- that's right, you guessed it -- directly to XMLSpy, the company's XML editing/modeling tool. (I love it when tools work well with one another, especially when they're from the same company.)

I tested the export functions on a number of files with different configurations, and the wizard proved extremely quick and easy to use. The import was excellent as well: easy to use, and it performed far better than I would have hoped.

DatabaseSpy also has a nice graphical interface for designing tables. It's not exactly a modeling application, because it doesn't have any logical and physical modeling capabilities. To get quick diagrams of your schema, just drag items from your tree view onto the design surface, and the application automatically keeps up with relationships as dependent tables are added to the diagram. It's clearly made to work with large models because it comes with a nice magnifier, such as that found in Embarcadero's ER/Studio enterprise modeling tool.

DatabaseSpy also can boast is its capability of querying tables directly from the design surface. This makes it more of a live-action schema designer, which can actually be a little scary in that it makes changing to live schemas far too easy. Although you can't push those changes directly to the database from the model designer, you can create a change script and run it manually, directly in DatabaseSpy.

Although I couldn't find any errors in the generated code itself, it comes out as a horrendous, unformatted, continuous stream of text that can be very hard to read. The same goes for the HTML generated by the export wizard. It's just plain HTML, and there's no way to alter it before the page is created, so I'm not sure of the usefulness of it at this point. You can alter it any way you like after the file is created, though, and a simple CSS should take care of pretty much any formatting you need.

For all its strengths, DatabaseSpy falls a bit short in terms of tools that DBAs might like to see. Among other things, it lacks a debugger; it can't run execution plans; it doesn't offer source control, and it won't let you work with stored procedures and functions directly from the database.

Click for larger view.

Despite its lack of admin-oriented features, Altova DatabaseSpy 2007 is an impressive offering, and priced reasonably compared with its competition. Its true power currently resides is in its ease of use for end-users and its import/export functionality. Bear in mind that this is only the first release, so keep your eye on this tool: It's going places -- although the company may still be trying to shake out exactly who its target audience is.

InfoWorld Scorecard
Value (10.0%)
Manageability (25.0%)
Performance (15.0%)
Reliability (15.0%)
Setup (10.0%)
Ease of use (25.0%)
Overall Score (100%)
Altova DatabaseSpy 2007 8.0 8.0 9.0 8.0 9.0 8.0 8.3

Copyright © 2007 IDG Communications, Inc.

How to choose a low-code development platform