Query Analyzer Tips and Tricks
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 Templates\SQL 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.