Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
If you’ve worked with Microsoft SQL Server at all, you’ve run across SQL
Query Analyzer. Of course, this tool is essential for running ad hoc queries
and executing SQL statements. But have you ever taken the time to really
investigate its capabilities? The SQL Server developers built a lot of
functionality into Query Analyzer, not all of which is obvious to the casual
user. In this article, I’ll offer you ten bits of Query Analyzer that you
might not have looked at already.
1. Getting Database Object Information
You probably know that SQL Server stores metadata about all of the objects in
a database. The system tables contain a wealth of information about column
names, data types, identity seeds, and so on. But did you know that you can get
that information with a single keystroke via Query Analyzer? Highlight the
object name in any SQL statement and press Alt+F1. Figure 1 shows the results
for a SQL Server table. If you don’t have anything highlighted, Alt+F1 will give
you information about the database itself. For an equally neat trick,
highlight a SQL keyword and press Shift+F1; you’ll go straight to the Books
Online page that describes that keyword.
2. Executing Part of a SQL Statement
Sometimes it’s convenient to execute only part of a complex SQL statement
that you’re developing. For example, you might be working on a stored procedure
that batches many statements together, or a query that contains a subquery. No
problem! Just highlight the part that you want to execute, and press F5 (or
press the Execute Query toolbar button if you’re a mouse sort of person). Query
Analyzer will only execute the highlighted text. You can parse the highlighted
text without executing it by pressing Ctrl+F5.
3. Alter Objects Fast with the Object Browser
I can’t possibly be the only one who’s ever needed to fix an existing stored
procedure. Fortunately, using Query Analyzer means never having to write the
ALTER PROC statement by hand. First, display the Object Browser by pressing F8,
if it’s not already showing. Expand the tree to show the object that you’re
interested in (this tip works with any object, not just stored procedures).
Right-click on the object and select Script Object to New Window As -> Alter.
Query Analyzer will open a new query window, and build the necessary ALTER PROC
statement for you.
If you’ve never looked at them, take a few minutes to explore the Object
Browser shortcut menus. You can send object scripts to the clipboard or to a
file, execute stored procs, or build CREATE or DROP SQL statements, among other
options.
4. Drag and Drop from the Object Browser
The Object Browser is also a drag and drop source. Drag a table to a query
window, and you get the table’s name. Drag the Columns node under a table, and
you get all of the columns from the table, separated by commas. Drag a single
column and you get the column name. Judicious use of this technique can make
fast work of building things like INSERT INTO statements, as well as avoid
spelling errors.
5. Templates are Your Friend
Query Analyzer supports templates – boilerplate files containing SQL
statements – to help you build tricky SQL more quickly. These files have the
extension .tql, and they’re stored in folders underneath the TemplatesSQL Query
Analyzer directory if you’ve done a full client tools install. Use
Ctrl+Shift+Ins or Edit -> Insert Template to open a template into the current
query window. Many templates contain parameters, which are delimited by angle
brackets. Press Ctrl+Shift+M and you’ll get the Replace Template Parameters
dialog box, as shown in Figure 2.
Templates are just plain text files. If you find yourself needing to
frequently insert some complex chunk of SQL, you can create and save your own
template file to make life simpler in the future.
6. What’s Up With This Query?
If you’re faced with a query that has a performance issue, query analyzer is
your first stop for gathering information. You have easy access to four
different ways of looking at query performance:
- Ctrl+L will show you the estimated execution plan before you run the
query. - Ctrl+K will show you the actual execution plan after you run the query.
- Ctrl+Shift+T will open the trace pane, showing you the trace events on the
server as you run the query. - Ctrl+Shift+S will show you client-side statistics after you run the
query.
There are items on the Query menu for each of these panes, just in case your
brain is already too full to hold new shortcut keys.
7. Customize Connection Properties
When you fire up Query Analyzer and connect it to a database, it sets a few
defaults – for instance, ANSI style null handling. If you’d like to tweak the
connection properties for your own server environment, select Connection
Properties from the Edit menu to open the dialog box shown in Figure 3, and
click to your heart’s content.
8. Get Back Wide Results
If you run a query that returns a column with lots of data, you’ll discover
that Query Analyzer truncates the results after the first 256 characters. This
is especially annoying when you’re working with FOR XML queries, which can
return thousands of characters in an XML document format. Fortunately, this
limitation is easy to modify. Select Options from the Tools menu, and navigate
to the Results tab of the Options dialog box. Enter a new value for the Maximum
Characters Per Column property and you’re all set. While you’re there, take the
time to click around the rest of the Options dialog box; Query Analyzer lets you
tweak quite a few things to match your own preferences.
9. Query Debugging
Query Analyzer contains a complete debugger. You can single step through
stored procedures, inspect the value of local and global variables, supply
values for parameters, inspect the callstack when multiple procedures are
nested, and so on. The easiest way to get started is to right-click a stored
procedure in the Object Browser window and select Debug . Figure 4 shows the
debugging environment in action. This can be a real lifesaver when you’re trying
to figure out what’s wrong with a complex stored procedure.
10. The Tools Menu the Way You Want It
The Query Analyzer Tools menu is extensible. Select Customize from the Tools
menu to open the Customize dialog box, and switch to the Tools tab. Here you can
enter new values to appear on the menu, and specify the executable file to call
for each entry. You can also pass the server name, database name, or user name
to the external utility.
And Yes, There’s More
I hope this article has convinced you that Query Analyzer can be a powerful
tool for creating, executing, and debugging SQL statements. There are plenty of
features that I didn’t cram into this top ten. For example, the Query Analyzer
editor supports bookmarks, forcing case, adjusting tabs, and more. If this is a
product that you use on a frequent basis, you owe it to yourself to keep digging
and find out how to use it more efficiently. The time spent learning will be
amply repaid.
About the Author
Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the lead developer for Larkware. Check out his latest book, Coder to Developer from Sybex. When
he’s not writing code, Mike putters in the garden on his farm in eastern
Washington state.
