Overview of SQL Query Analyzer

Microsoft® SQL Server™ 2000 SQL Query Analyzer is a graphical tool that allows you to:

  • Create queries and other SQL scripts and execute them against SQL Server databases. (Query window)

  • Quickly create commonly used database objects from predefined scripts. (Templates)

  • Quickly copy existing database objects. (Object Browser scripting feature)

  • Execute stored procedures without knowing the parameters. (Object Browser procedure execution feature)

  • Debug stored procedures. (T-SQL Debugger)

  • Debug query performance problems. (Show Execution Plan, Show Server Trace, Show Client Statistics, Index Tuning Wizard)

  • Locate objects within databases (object search feature), or view and work with objects. (Object Browser)

  • Quickly insert, update, or delete rows in a table. (Open Table window)

  • Create keyboard shortcuts for frequently used queries. (custom query shortcuts feature)

  • Add frequently used commands to the Tools menu. (customized Tools menu feature)

You can run SQL Query Analyzer directly from the Start menu, or you run it from inside SQL Server Enterprise Manager. You can also run SQL Query Analyzer from the command prompt by executing the isqlw utility.


Using isqlw

Use the isqlw utility to run SQL Query Analyzer from the command prompt. Options are provided for specifying server name, database name, login information, input and output files, and other information. These options enable you to start a preconfigured SQL Query Analyzer.

See Also

isqlw Utility


Connecting to SQL Server

Connect to an instance of Microsoft® SQL Server™ to open a query window in SQL Query Analyzer. If you access SQL Query Analyzer through a connection in SQL Server Enterprise Manager, a query window opens automatically.

If you access SQL Query Analyzer without first establishing a connection, the Connect to SQL Server dialog box is displayed to allow you to specify a database server. This dialog box is also accessible from the toolbar, and from the Connect command on the File menu.

To connect to SQL Server

  1. In the Connect to SQL Server dialog box, enter the name of the database server in the SQL Server box.

    To select the local server, select (local).

    To specify another server or another instance of the server, enter the server name in the SQL Server box. Click the browse button (...) to display a list of active servers. The servers are listed using the format servername\instancename.

  2. Click Windows NT Authentication to connect using Windows NT Authentication.

    -Or-

    Click SQL Server Authentication to connect using SQL Server Authentication.

After you have connected to an instance of SQL Server, you can establish additional connections through the Connect to SQL Server dialog box or you can open a new query. In the latter case, the Connect to SQL Server dialog box is not displayed.

Attempting to connect to an instance of SQL Server version 7.0, running on the same computer as SQL Query Analyzer and SQL Server 2000, may fail under certain conditions. To avoid this problem, make sure that the shared memory protocol option within SQL Server Client Network Utility is disabled before trying to connect. For more information, see How to start the Client Network Utility (Windows).


Using SQL Query Analyzer Windows

SQL Query Analyzer provides several windows in which to work:

  • Query window

  • T-SQL Debugger window

  • Open Table window
Query Window

The title bar of the query window displays the name of the database server, the name of the current database, the current login name, and the query name. If the query is saved to a file, the complete path to the file is shown.

The query window is composed of multiple panes. The Editor pane is a text editor where you can enter Transact-SQL statements. The Results pane displays returned result sets. The Messages pane displays error messages. The Execution Plan pane displays a graphical representation of an execution plan. The Trace pane displays server trace information. The Statistics pane displays statistics information.

Click a pane to make it the active pane. Alternatively, press SHIFT+F6 to move between panes.

T-SQL Debugger Window

The Transact-SQL Debugger window is composed of multiple panes. The Code pane displays the SQL statements being debugged. In addition, the Transact-SQL debugger presents separate output windows for local and global variables, and for the output (result set) of the query. Note that global variable is a legacy term for the Transact-SQL built-in functions whose names start with @@.

Open Table Window

The Open Table window displays the columns and rows from a table in a grid. You can modify the data in the grid. You can also insert and delete rows.

Attempts to insert, delete, or update a column referenced by a computed column may fail under certain conditions. If an index exists on the computed column, then user option ARITHABORT must be enabled by default before you can edit the referenced column. To enable ARITHABORT, execute the following set of commands on your server:

sp_configure 'user options', 64
RECONFIGURE
GO


SQL Query Analyzer Icons

This table lists the icons displayed in the SQL Query Analyzer toolbar.

Icon | Description
 | Open a new query window.
 | Open a query file.
 | Save a query to a file.
 | Insert Template.
 | Cut.
 | Copy.
 | Paste.
 | Clear the Editor pane.
 | Find.
 | Undo.
 | Result Target Selector. This option has four states: result to text (display execution plan off), result to text (display execution plan on), result to grid (execution plan off), result to grid (execution plan on).
 | Check syntax only.
 | Execute a query.
 | Cancel a query.
 | Change database.
 | Display Object Browser in SQL Query Analyzer.
 | Display the estimated execution plan.
 | Display the Object Search dialog box.
 | Display connection properties.
 | Show/hide the Results pane.
 | Display the Connect to SQL Server dialog box.
 | Manage Windows.
 | Cascade.
 | Disconnect.
 | Disconnect All.
 | Horizontal Tile.
 | Index Tuning Wizard
 | Replace Templates
 | Repeat Search.
 | T-SQL Help.
 | Vertical Tile.

Configuring the Toolbar

You can custom configure the toolbar.

To configure the toolbar

  1. Right-click the toolbar.

  2. In the Customize Toolbar dialog box, add or remove the buttons and separators to be displayed on the toolbar. Use Move Up and Move Down to rearrange the order.

To restore the toolbar to its default configuration

  • Click Reset.


Transact-SQL Debugger Icons

This table lists the icons displayed in the Transact-SQL Debugger toolbar.

Icon | Description
 | Go
 | Toggle Breakpoint
 | Remove All Breakpoints
 | Step Into
 | Step Over
 | Step Out
 | Run to Cursor
 | Restart
 | Stop Debugging
 | Watch
 | Callstack
 | Auto Rollback
 | Help


Status Bar Information

SQL Query Analyzer has five status bars, which provide information about activity or connections in SQL Query Analyzer windows.

SQL Query Analyzer window - The status bar in the SQL Query Analyzer application window displays:

  • General information about activity in the window, including descriptions of selected menu commands.

  • Total number of connections consumed by the current user.

  • CAPS lock setting.

  • NUM lock setting.

  • Insert/Overwrite mode setting.

Query window - The Query window status bar displays:

  • Status of the current operation.

  • Name of the current database server.

  • Current user and server process identifier (ID).

  • Name of the current database.

  • Amount of execution time for the last query or the currently executing query.

  • Number of rows returned. When results are displayed in a grid and the Results pane is the active pane, the grid number is also shown.

  • Current position of the insert cursor in the window. When results are displayed in a grid and the Results pane is the active pane, the position of the current grid cell is shown.

Object Search - The Object Search window status bar displays:

  • Status of the current operation.

  • Name of the current database server.

  • Current user and server process ID.

  • Amount of search time for the last search or currently executing search.

  • Number of rows in the search result.

Open Table window - The Open Table window status bar displays:

  • Name of the current database server.

  • Current user and server process ID.

  • Name of the current database.

  • Number of rows returned.

  • Position of the current grid cell.

T-SQL Debugger - The Transact-SQL Debugger window status bar displays:

  • Status of the current operation.

  • Connected.

  • Automatic rollback of transactions.

  • Current position of the insert cursor in the window.


Color Coding in SQL Query Analyzer

The code entered in the Editor pane is colored by category. This table lists the default colors and what they indicate.

Color | Category
Red | Character string
Dark Red | Stored Procedure
Green | System Table
Dark Green | Comment
Magenta | System Function
Blue | Keyword
Gray | Operator

Note You can change these defaults by selecting the Fonts tab on the Options dialog box.


SQL Query Analyzer Keyboard Shortcuts

This table displays the keyboard shortcuts available in SQL Query Analyzer.

Activity | Shortcut
Bookmarks: Clear all bookmarks. | CTRL-SHIFT-F2
Bookmarks: Insert or remove a bookmark (toggle). | CTRL+F2
Bookmarks: Move to next bookmark. | F2
Bookmarks: Move to previous bookmark. | SHIFT+F2
Cancel a query. | ALT+BREAK
Connections: Connect. | CTRL+O
Connections: Disconnect. | CTRL+F4
Connections: Disconnect and close child window. | CTRL+F4
Database object information. | ALT+F1
Editing: Clear the active Editor pane. | CTRL+SHIFT+DEL
Editing: Comment out code. | CTRL+SHIFT+C
Editing: Copy. You can also use CTRL+INSERT. | CTRL+C
Editing: Cut. You can also use SHIFT+DEL. | CTRL+X
Editing: Decrease indent. | SHIFT+TAB
Editing: Delete through the end of a line in the Editor pane. | CTRL+DEL
Editing: Find. | CTRL+F
Editing: Go to a line number. | CTRL+G
Editing: Increase indent. | TAB
Editing: Make selection lowercase. | CTRL+SHIFT+L
Editing: Make selection uppercase. | CTRL+SHIFT+U
Editing: Paste. You can also use SHIFT+INSERT. | CTRL+V
Editing: Remove comments. | CTRL+SHIFT+R
Editing: Repeat last search or find next. | F3
Editing: Replace. | CTRL+H
Editing: Select all. | CTRL+A
Editing: Undo. | CTRL+Z
Execute a query. You can also use CTRL+E (for backward compatibility). | F5
Help for SQL Query Analyzer. | F1
Help for the selected Transact-SQL statement. | SHIFT+F1
Navigation: Switch between query and result panes. | F6
Navigation: Switch panes. | Shift+F6
Navigation: Window Selector. | CTRL+W
New Query window. | CTRL+N
Object Browser (show/hide). | F8
Object Search. | F4
Parse the query and check syntax. | CTRL+F5
Print. | CTRL+P
Results: Display results in grid format. | CTRL+D
Results: Display results in text format. | CTRL+T
Results: Move the splitter. | CTRL+B
Results: Save results to file. | CTRL+SHIFT+F
Results: Show Results pane (toggle). | CTRL+R
Save. | CTRL+S
Templates: Insert a template. | CTRL+SHIFT+INSERT
Templates: Replace template parameters. | CTRL+SHIFT+M
Tuning: Display estimated execution plan. | CTRL+L
Tuning: Display execution plan (toggle ON/OFF). | CTRL+K
Tuning: Index Tuning Wizard. | CTRL+I
Tuning: Show client statistics | CTRL+SHIFT+S
Tuning: Show server trace. | CTRL+SHIFT+T
Use database. | CTRL+U


Managing SQL Query Analyzer Windows

In SQL Query Analyzer, you can customize the windows and control the behavior of the Editor pane and the Results pane. You can also specify fonts to use and manipulate the size of the Results pane relative to the Editor pane.

Specifying input and output options

You can control the look and behavior of the Query window through the Options dialog box, which is accessible from the Tools menu.

To customize the Editor pane, use the Editor tab in the Options dialog box:

  • To set the maximum number of undo buffers and the maximum cumulative size of all undo buffers.

  • To set the tab size (in spaces).

  • To control whether tabs are saved as tab characters or spaces.

  • To enable and disable dragging text in the Editor pane.

  • To specify the default non-Unicode file open format.

  • To specify whether the Results pane appears on a separate tab in the Query window (tabbed mode) or is displayed below the Editor pane (splitter mode). If tabbed mode is selected, you can also control whether the Results tab appears automatically after a query executes, and whether the Query window tabs are displayed at the top or the bottom of the window.

To customize the Results pane, use the Results tab in the Options dialog box:

  • To specify the default destination for results. The options are Results to Text, Results to Grids, and Results to File.

  • To specify the result set format.

  • To specify the maximum width for result set columns.

  • To specify whether to include column headers in the output and whether numbers are right-aligned.

  • To specify whether to scroll the Results pane as results are received.

  • To control various actions, such as discarding results or playing a sound, when execution completes.
Specifying fonts

Specify fonts used for text in the Editor pane, the Results text pane, the Results grid pane and Open Table window, the Execution Plan pane, the Statement Profile, the Statistics pane, and the Object Browser. Use the Fonts tab of the Options dialog box, accessible from the Tools menu, to specify the fonts to use. You can also right-click these panes and select Fonts from the Context menu.

Moving the splitter

When the Query window is split, you can change the relative size of the Editor pane and the Results pane by positioning the mouse on the split bar and dragging it up or down.

To position the pointer on the split bar, press CTRL+B.

Showing/Hiding the Results Pane

If the Query window is in split mode, you can toggle the display of the Results pane by clicking the Show Results Pane button on the toolbar.


Managing Options in SQL Query Analyzer

You can control the values of SET options for the current connection and for all new connections. You can also set general connection options, such as timeout settings, for new connections. To set connection options:

  • Select Current Connection Properties from the Query menu to control SET options for the current connection.

  • Select Options from the Tools menu and use the Connection Properties tab to control SET options for all new connections.

  • Select Options from the Tools menu and use the Connections tab to set general connection options.

See Also

Using SET Options in SQL Query Analyzer

Specifying General Connection Operations


Using SET Options in SQL Query Analyzer

Connection-level options are specified with SET statements. These are default settings for SET options in SQL Query Analyzer.

Option | Default Setting
Set nocount | OFF
Set noexec | OFF
Set parseonly | OFF
Set concat_null_yields_null | ON
Set rowcount | 0
Set ansi_defaults | ON
Set arithabort | ON
Set showplant_text | OFF
Set statistics time | OFF
Set statistics 10 | OFF
Set ansi_nulls | ON
Set ansi_null_dflt_on | ON
Set ansi_padding | ON
Set ansi_warnings | ON
Set cursor_close_on_commit | OFF
Set implicit_transactions | OFF
Set quoted_identifier | ON

Important The SQL Query Analyzer default settings of Set ansi_defaults, Set arithabort, and Set quoted_identifier differ from the default settings in earlier releases of Microsoft® SQL Server™.

Use the Current Connection Properties dialog box to specify the settings for the current connection. Use the Connection Properties tab of the Options dialog box to specify the settings for all new connections.


Specifying General Connection Operations

You can specify the default settings for general operations, such as time-out values and network package sizes. You can also specify default behavior for operations, such as whether to use regional settings to display currency, numbers, dates, and time, or whether to disconnect after each query.

Use the Connections tab of the Options dialog box to define these defaults.


Using the Editor Pane

The Editor pane in SQL Query Analyzer is a text-editing window used to enter and execute SQL statements. To enter code, you can:

  • Type SQL statements directly in the Editor pane.

  • Open a saved SQL script. The contents are displayed in the Editor pane, where they can be edited.

  • Open a template file. The contents are displayed in the Editor pane, where they can be edited.

  • Use the scripting features of Object Browser to copy SQL statements for the selected database object into the Editor pane.


Editing SQL Statements

The Editor pane in SQL Query Analyzer provides various tools to help you create and edit SQL statements, including the standard editing commands: Undo, Cut, Copy, Paste, and Select All.

You can also find and replace text, move the input cursor to a particular line, insert and remove indentation, force case, and insert and remove comment marks.

In addition, you can view Transact-SQL Reference topics from Microsoft SQL Server Books Online and copy the syntax into the Editor pane.


Moving and Copying Text

This procedure describes how to move or copy text in the Editor pane.

To move or copy text

  1. Select the text to be moved or copied. Click Select All to select all of the text in the Editor pane.

  2. Click Cut to move text or Copy to copy text.

  3. Click the new location to mark the insertion point for the text.

  4. Click Paste.

Alternatively, you can select the text to be moved and drag it to the new location.

Note A drag-and-drop operation can be used within a pane, from one pane to another, and even from SQL Query Analyzer to other applications. In addition, database objects can be dragged from Object Browser and the Object Search dialog box and dropped into the query window.


Undoing Mistakes

This procedure describes how to undo a mistake in the Editor pane.

To undo a mistake

  • Click the Undo button or press CTRL+Z.

Each time you click Undo, the previous action is rolled back. The maximum number of undo operations is 20.


Clearing the Editor Pane

This procedure describes how to remove all text from the Editor pane.

To remove all text from the Editor pane

  • Click Clear Query Window, or press CTRL+SHIFT+DEL.


Finding and Replacing Text

These procedures describe how to find and replace text in the Editor pane.

To find text

  1. Select Find from the Query menu.

  2. Enter the text to be found.

  3. Specify the search direction.

  4. For a case sensitive search, select Match Case.

To replace text

  1. Select Replace from the Query menu.

  2. Enter the text to be found.

  3. Enter the replacement text.

  4. Specify the search direction.

  5. For a case sensitive search, select Match Case.

  6. Replace occurrences individually by clicking Find Next and then Replace.

    -Or-

    Replace all occurrences by clicking Replace All.


Going to a Line Number

This procedure describes how to go to a line number in the Editor pane.

To go to a line number

  1. From the Edit menu, click Go to Line.

  2. In the Go To dialog box, enter the line number.

If you specify a number greater than the total number of lines available, the destination will be the bottom of the window.


Using Bookmarks

You can insert bookmarks on the left side of the Editor pane to help you locate lines in your code.

To insert or remove bookmarks

  • To insert a bookmark at the current line, press CTRL+F2.

    -Or-

    To remove the bookmark at the current line, press CTRL+F2.

  • To remove all bookmarks, press CTRL+SHIFT+F2.

To navigate between bookmarks

  • Press F2 to move to the next bookmark.

    -Or-

    Press SHIFT+F2 to move back to the previous bookmark.


Forcing the Case of Text

This procedure describes how to force the case of text in the Editor pane.

To force the case of text

  1. Select the text for which you want to change case.

  2. To make the selected text lowercase, press CTRL+SHIFT+L.

    -Or-

    To make the selected text uppercase, press CTRL+SHIFT+U.


Adding and Removing Indentation

These procedures describe how to change indentation in the Editor pane.

To indent one line of text

  1. Set an insertion point to the left of the text to be indented.

  2. Press TAB.

To remove indentation from one line of text

  1. Make an insertion point to the left of the text.

  2. Press SHIFT+TAB.

To add or remove indentation for a block of text

  1. Select the text.

  2. Press TAB to indent the entire block.

    -Or-

    Press SHIFT+TAB to remove indentation for the entire block.


Adding and Removing Comments

These procedures describe how to add and remove comments in the Editor pane.

To comment out text

  1. Select the text to comment out.

  2. Press CTRL+SHIFT+C.

To remove comment marks

  1. Select the text from which comment marks are to be removed.

  2. Press CTRL+SHIFT+R.


Viewing Transact-SQL Help

This procedure describes how to view Transact-SQL help.

To view Transact-SQL help

  • In the Editor pane, select a Transact-SQL statement, function, stored procedure, or other Transact-SQL element, and press SHIFT+F1 to view information about the selected text.


Using SQL Files

You can save query definitions and other SQL scripts for reuse. The default extension for these files is .sql.

You can also create templates, which are boilerplate scripts used to create objects in a database. The extension for template files is .tql.


Opening SQL files

This procedure describes how to open a SQL script and display code in the Editor pane.

To open a script and display the code in the Editor pane

  1. Open a Query window

  2. Click Load SQL Script on the toolbar.

  3. In the Open Query File dialog box, specify the location and name of the file to be opened.

  4. If necessary, specify a file format. If you specify Auto, the appropriate file format is used automatically.


Saving SQL Statements to a File

This procedure describes how to save the contents of the Editor pane to a file.

To save the contents of the Editor pane to a file

  1. Make the Editor pane the active pane.

  2. Click Save on the toolbar.

  3. If the file has never been saved, the Save Query dialog box is displayed. Specify a location, file name, file extension, and file format.

You can also save result sets to a file by making the Results pane the active pane before saving, or by selecting Results to File from the Query menu before executing the query.


Using Templates in SQL Query Analyzer

Templates are boilerplate files containing SQL scripts that help you create objects in the database. Microsoft® SQL Server™ 2000 provides a variety of templates in the Templates\SQL Query Analyzer directory. Among the templates provided are those that create databases, tables, views, indexes, stored procedures, triggers, statistics, and functions. In addition, there are templates that help you to manage extended properties, linked servers, logins, roles, and users, and to declare and use cursors.

The template scripts provided with SQL Query Analyzer contain parameters to help you customize the code. Template parameter definitions use this format:

<parameter_name, data_type, value>

where

  • parameter_name is the name of the parameter in the script.

  • data_type is the data type of the parameter.

  • value is the value that is to replace every occurrence of the parameter in the script.

Use the Replace Template Parameters dialog box to insert values into the script.

Note You can use the Replace Template Parameters dialog box to specify values any time a parameter definition is used in code. For example, when you execute a function from Object Browser, the function that is written to the Editor pane contains parameter definitions for any arguments in the function. You can, therefore, use the Replace Template Parameters dialog box to specify argument values.

To use a template

  1. Open a Query window.

  2. Click Insert Template on the toolbar.

  3. In the Insert Template dialog box, specify the template to open. By default, template files have the extension .tql.

  4. When the template is displayed in the Editor pane, select Replace Template Parameters from the Edit menu.

  5. In the Replace Template Parameters dialog box, specify values for the parameters.

  6. To insert the specified values into the script in the Editor pane, click Replace All.

  7. Save the file under a different name.


Running Queries in SQL Query Analyzer

There are several ways to run queries in SQL Query Analyzer. You can execute SQL statements entered or loaded into the Editor pane, or you can use the various methods available for executing stored procedures.

Executing SQL Statements in SQL Query Analyzer

Executing Stored Procedures in SQL Query Analyzer

Canceling a Long-Running Query

Resolving Errors in SQL Query Analyzer

Returning Result Sets in SQL Query Analyzer


Executing SQL Statements in SQL Query Analyzer

You can execute a complete script or only selected SQL statements in SQL Query Analyzer.

  • Execute a complete script by creating or opening the script in the Editor pane and pressing F5.

  • Execute only selected SQL statements by highlighting the lines of code in the Editor pane and pressing F5.

You can also easily execute a SELECT statement containing a built-in function. Right-click the function in Object Browser, select Script Object to New Window As, and select Execute. A SELECT statement containing the function is displayed in a new Editor pane. If the function contains arguments, they are displayed in template parameter format. Press CTRL+SHIFT+M to display the Replace Template Parameters dialog box and supply values for the function arguments. Press F5 to execute the SELECT statement.


Executing Stored Procedures in SQL Query Analyzer

SQL Query Analyzer provides multiple ways to execute stored procedures.

  • You can enter the statement to execute the stored procedure directly in the Editor pane and press F5. If the statement that executes the procedure is the first in the batch, you can omit the EXECUTE (or EXEC) statement; otherwise, EXECUTE is required.

  • You can right-click a stored procedure in Object Browser or Object Search dialog box and select Open to invoke the Execute Procedure dialog box. This dialog box lists any parameters and provides a text box where you specify parameter values.


Canceling a Long-Running Query

  • Click Cancel Executing Query in the Query menu or press ALT+BREAK. Notification of the cancellation appears in the Messages pane and in the status bar.


Resolving Errors in SQL Query Analyzer

SQL Query Analyzer provides several tools to help you prevent and resolve errors.

  • Use the color-coding in the Editor pane to help eliminate errors.

    Using the default colors as examples, if you type a keyword not displayed in blue, the keyword may be misspelled. If much of your code is displayed as red, you might have omitted the closing quotation mark for a character string.

  • To parse syntax without executing the code, click the Parse button.

  • If an error occurs, double-click the error message to locate the line that contains the error in your code.

  • To debug stored procedures, use the Transact-SQL Debugger.


Returning Result Sets in SQL Query Analyzer

SQL Query Analyzer provides multiple ways for returning result sets.

  • Within the Query window, you can choose whether the Results pane should be displayed below the Editor pane or on a separate Results tab.

  • You can control whether the Results pane is displayed as text or as one or more grids.

  • You can choose to have the results saved to a file.

  • If you open a table in Object Browser or from the Object Search dialog box, the results are displayed in the Open Table window. You can perform insert, update, and delete operations in the Open Table window.


Entering Data in SQL Query Analyzer

In addition to the Editor pane of the Query window, in which you can type INSERT, UPDATE, and DELETE statements, SQL Query Analyzer provides the Open Table window, which is a graphical interface for viewing or modifying data.

From either Object Browser or the Object Search dialog box, right-click the table that contains the data to be updated and select Open from the Context Menu.

To insert a row

  1. Right-click in the Open Table window and select Add from the Context Menu.

    An empty row is inserted at the bottom of the window.

  2. Fill in the data in the columns of the new row.

To update a row

  1. Locate the row to be modified and enter the changes. If you right-click in the cell your are editing, you have access to Undo, Cut, Copy, Paste, Delete, and Select All commands on the Context menu.

  2. Move off the row to save the changes.

To delete a row

  • Right-click the row to be deleted and select Delete from the Context menu.


Printing in SQL Query Analyzer

You can print the contents of the Editor pane or, if text output is specified, the contents of the Results pane.

To print in SQL Query Analyzer

  • Click the pane to be printed and select Print from the File menu.


Analyzing Queries

Among the most important features of SQL Query Analyzer are the tools to help you analyze your queries for optimal performance. These tools include:

  • Show Query Execution Plan.

  • Show Estimated Execution Plan.

  • Show Server Trace.

  • Show Client Statistics.

  • Index Tuning Wizard.

See Also

Viewing the Query Execution Plan

Viewing the Estimated Query Execution Plan

Viewing Server Trace Information

Viewing Client Statistics Information

Tuning Indexes


Viewing the Query Execution Plan

  1. If there is no query in the query pane, open a saved query or create a new query.

  2. On the Query menu, click Show Execution Plan or press CTRL+K to turn it on.

    Note This option works only when connected to an instance of Microsoft® SQL Server™ version 7.0 or later.

  3. Execute the query by pressing F5, or on the Query menu, click Query Execute.

  4. Position the cursor over graphical elements to reveal additional execution plan information.


Viewing the Estimated Query Execution Plan

  1. If there is no query in the query pane, open a saved query or create a new query.

  2. On the Query menu, click Display Estimated Execution Plan or press CTRL+L.

  3. If you have referenced temporary objects, no estimated query execution plan will be displayed.

    Note This option works only when connected to an instance of Microsoft® SQL Server™ version 7.0 or later.

  4. Position the cursor over graphical elements to reveal additional execution plan information.


Viewing Server Trace Information

  1. Open a saved query or create a new query in the query pane.

  2. On the Query menu, click Show Server Trace.

  3. Execute the query by pressing F5 or Query Execute from the menu.

  4. Click the Trace tab to display trace information.

For more information about events, see Monitoring with SQL Profiler Event Categories.


Viewing Client Statistics Information

  1. Open a saved query or create a new query in the query pane.

  2. On the Query menu, click Show Client Statistics.

  3. Execute the query by pressing F5 or on the Query menu, click Query Execute.

  4. Click the Statistics tab to display statistics information.

See Also

Query Window Statistics Pane


Tuning Indexes

  1. If there is no query in the query pane, open a saved query or create a new query.

  2. Select the query text to tune.

  3. On the Query menu, click Index Tuning Wizard or press CTRL+I. To perform index tuning, you must be a member of the sysadmin fixed server role.

    Note This option works only when connected to an instance of Microsoft® SQL Server™ version 7.0 or later.

  4. Follow the instructions in the Index Tuning Wizard. On the Specify Workload page, select Query Analyzer selection to tune the query in the Query window. Continue through the wizard pages. The wizard recommends the best index configuration for the query.

  5. When you have completed the steps required to tune your index, you may apply the recommendations immediately, schedule a job to apply them at a certain time, or save them to a file in the form of a Transact-SQL script.


Using Object Browser

Object Browser is a tree-based tool used to navigate the objects in a database. In addition to navigation, Object Browser offers object scripting, stored procedure execution, and access to table and view objects.

Object Browser consists of two panes:

  • Objects pane, which lists objects within database and common objects, such as built-in functions and base data types.

  • Templates pane, which provides access to the Templates directory.

See Also

Adding Objects to Code from Object Browser

Querying from Object Browser

Running Stored Procedures from Object Browser

Editing Objects from Object Browser

Using the Scripting Feature in Object Browser

Setting Extended Properties from Object Browser


Adding Objects to Code from Object Browser

Use the drag-and-drop feature of Object Browser to add objects to code in the Editor pane.

To add a single object

  1. In Object Browser, expand a server, and then expand a database.

  2. Expand a folder, click the object, and then drag the object to the position in the Editor pane.

For example, to build a query that selects the CategoryID column from the Categories table, type SELECT in the Editor pane and open the folder for the Northwind database. Drag CategoryID to the position following SELECT in the Editor pane.

To add all the objects in a folder

  1. In Object Browser, expand a server, and then expand a database.

  2. Click the folder, and then drag the folder to the Editor pane.

For example, to build a select list that contains all of the columns in the Category table, drag the Columns folder from the Category table to the Editor pane.


Querying from Object Browser

You can use Object Browser to execute SELECT * queries in SQL Query Analyzer.

To execute a query from Object Browser

  1. In Object Browser, expand a server, and then expand a database.

  2. Expand a folder, right-click the table, and then click Open.

The query results are displayed in a separate results window.


Running Stored Procedures from Object Browser

Use Object Browser to run a stored procedure and be prompted for parameters.

To run a stored procedure

  1. In Object Browser, expand a server, and then expand a database.

  2. Expand the Stored Procedures folder, right-click the stored procedure, and then click Open to display the Execute Procedure dialog box.

  3. Click a parameter in the Parameters list, and then type a value in the Value box. Do this for each parameter you want to use.

  4. Click Execute.

The EXECUTE statement for the stored procedure is displayed in the Editor pane and the result is displayed in the Results pane.


Editing Objects from Object Browser

You can use Object Browser to alter objects in the database.

To edit an object from Object Browser

  1. In Object Browser, expand a server, and then expand a database.

  2. Right-click the object and click Edit.

    An ALTER statement for the selected object is displayed in the Editor pane. For example, if the selected object is a stored procedure, an ALTER PROCEDURE statement is provided.

  3. Use the ALTER statement to specify the changes.

  4. Execute the ALTER statement.

To delete objects from Object Browser

  • Right-click the object, and then click Delete.

Unlike the Edit command, the Delete command does not display a DROP statement for you to modify and execute. Instead, you are asked to verify that you want to delete the object. If you click OK, the object is deleted.


Using the Scripting Feature in Object Browser

Object Browser provides scripting functionality for many of the displayed objects. The operations supported vary depending upon the type of object. For example, table objects can generate scripts containing SELECT statements, data definition statements (such as CREATE), or data manipulation statements (such as INSERT). Views can only generate SELECT statements or data definition statements.

There are three options for scripting destinations:

  • Script Object to New Window

  • Script Object to File As

  • Script Object to Clipboard

For any of these scripting destinations, you can specify the following operations.

Operation | Description
Create | Generates a CREATE statement for the object.
Alter | Generates an ALTER statement.
Drop | Generates a DROP statement.
Select | Generates a SELECT statement containing all of the tables columns in the select list
Insert | Generates an INSERT statement and provides placeholders for the values to be inserted.
Update | Generates an UPDATE statement and provides placeholders for values and the search condition.
Delete | Generates a DELETE statement and provides a placeholder for the search condition.
Execute | Generates an EXECUTE statement for a stored procedure.


Setting Extended Properties from Object Browser

Extended properties are properties that you can define on various objects in the database. Object Browser provides access to the Extended Property dialog box, where you can view existing properties for the selected object or define new properties.

To view or define extended properties for an object

  1. In Object Browser, expand a server, and then expand a database.

  2. Right-click the object and then click Extended properties.

To define a new property, enter a name for the property in the name box and a property value in the value box.


Using Object Search

Use the object search feature to find objects in the current database server. Object search is accessible through a button on the toolbar, and through the Object Search command on the Tools menu.

Important The object search feature is not available when connected to an instance of SQL Server 6.5.

To find an object

  1. Click the Object Search button to display the Object Search dialog box.

  2. In the Object name box, type the name of the object to be found, or use ALL to find all objects of the specified object types.

  3. For a case-sensitive search, select the Case-sensitive check box. Case-sensitive searches are valid only if the database server is case-sensitive.

  4. In the Database list, click the name of a particular database or click ALL.

  5. In the Hit Limit dialog box, enter the maximum number of search hits.

  6. In the Object type check boxes, select one or more types of objects to be located.

  7. To locate extended properties associated with the selected object types, select Extended Property.
    • You can search for all property names or a specific name.

    • You can search for all property values or a specific value.
  8. Click Find Now to execute the search.

The results are displayed in the lower portion of the dialog box.

Note You can drag one or more objects in the results to the Query window. You can also right-click in the results to display the Context menu, which enables you to open the selected object in the Open Table window, or perform various editing and scripting activities.


Using Shortcuts

SQL Query Analyzer provides various shortcuts for obtaining information and customizing the working environment. You quickly retrieve information about objects in the current database, create custom shortcuts for queries or stored procedures that you run frequently, and add commands to Tools menu.

See Also

Getting Database Object Information

Defining Custom Shortcuts

Customizing the Tools Menu


Getting Database Object Information

On the Help menu, Database Object Information to view sp_help information for the current database.

To view information about a specific object in the database

  1. Type and then highlight the object name in the query pane.

  2. On the Help menu, click Database Object Information.

The information is displayed in the Results pane.


Defining Custom Shortcuts

SQL Query Analyzer supports customizable keyboard shortcuts used to execute Transact-SQL statements. The following predefined key combinations cannot be modified:

Key Combination | Definition
ALT+F1 | sp_help
CTRL+1 | sp_who
CTRL+2 | sp_lock

To define query shortcuts

  1. On the Tools menu, click Customize.

  2. In the Customize dialog box, click the Custom tab.

  3. Type the Transact-SQL statement to be executed in the column next to a key combination. The statement can be a maximum of 1,024 characters in length.

    For example, the following code executes the sp_autostats stored procedure to enable autostatistics for the Customers table in the Northwind database.

    USE Northwind EXEC sp_autostats 'Customers', 'ON'

    This example executes a SELECT statement against the Customers table.

    SELECT * from Northwind..Customers

To remove all modifiable shortcuts

  • Click Reset All.


Customizing the Tools Menu

You can customize SQL Query Analyzer by adding commands to the Tools menu. For example, you can add executables like Notepad or osql, or the names of documents.

To add commands to the Tools menu

  1. On the Tools menu, click Customize.

  2. In the Customize dialog box, click the Tools tab.

  3. Click the add button. In the Menu contents list, type the name to appear on the Tools menu. This might be the name of an executable or the name of a document to be opened. Use the up and down arrows to position the name in the menu.

  4. In the Command box, enter the name and location of the file to be executed. You can use the browse (...) button to find the directory in which it resides.

    For example, to execute osql, enter this:

    C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe

    If using Microsoft® Windows NT® and want to specify Notepad, enter this:

    C:\WINNT\Notepad.exe
  5. Enter any arguments for the command in the Arguments box. For example, if the command is osql, you might specify the options -Usa.

    If the command is Notepad and you want the menu command to invoke a new document, leave this box blank. If you want to open a specific document in Notepad, specify the name of the document here.

  6. In the Initial Directory box, enter the location from which the command will be executed.


Using Transact-SQL Debugger

SQL Query Analyzer comes equipped with a Transact-SQL debugger that allows you to control and monitor the execution of stored procedures. The debugger supports traditional functionality such as setting breakpoints, defining watch expressions, and single stepping through procedures.

The Transact-SQL debugger in SQL Query Analyzer supports debugging against Microsoft® SQL Server™ 2000, SQL Server version 7.0, and SQL Server version 6.5 Service Pack 2.

Note It is not advisable to use the Transact-SQL debugger on a production server. While in step execution mode, the debugger can lock certain system resources that are needed by other processes.

See Also

Transact-SQL Debugger

Transact-SQL Debugger Icons


Starting the Debugger

T-SQL Debugger can only be run from within SQL Query Analyzer. Once started, the debugging interface occupies a window within that application.

Starting the debugger

There are two methods for starting the debugger. Each method requires that the target procedure be created and stored in a SQL Server database. The first method employs Object Browser. The second method relies on the Object Search tool.

To start the debugger from Object Browser

  1. On the Tools menu, click Object Browser.

  2. In Object Browser, click the Objects tab.

  3. Double-click to open the database in which your procedure is stored.

  4. Double-click to open the Stored Procedures folder.

  5. Right-click the stored procedure to debug. A Context menu will appear.

  6. Select Debug from the Context menu.

To start the debugger from Object Search

  1. On the Tools menu, click Object Search.

  2. In the Object Search window, enter the appropriate search parameters required to locate the procedure you are looking for. Click the Find Now button to display matches.

  3. In the results pane of the Object Search window, right-click the name of the stored procedure you wish to debug. A Context Menu appears.

  4. Select Debug from the Context Menu.

When the Transact-SQL Debugger starts, a dialog box appears, prompting you to set the values of input parameter variables. It is not mandatory that these values be set at this time. You will have the opportunity to make modifications once the debugger's interface appears. In the dialog box, click Execute to continue with your session.

Note SQL Query Analyzer does not support multiple instances of the debugger. Attempting to debug a second stored procedure will prompt you to cancel the currently active debugging session.

Due to connection constraints, it is not possible to create a new query while the debugger window is in the foreground. To create a new query, either bring an existing query window to the foreground or open a new connection to the database.


Interface Components

The Transact-SQL debugger interface consists of a tool bar, status bar, and a series of window panes. Many of these components have dual purposes, serving both as control and monitoring mechanisms.

Only limited functionality may be available from some of these components after a procedure has been completed or aborted. For example, you may not set breakpoints or scroll between entries in either of the variables windows when the procedure is not running.

Tool Bar

The toolbar resides at the top of the debugger interface. It consists of a series of buttons that may be used to control the starting, stopping, and incremental progression of an executing procedure.

Source Code Window

The source code window displays the code from the procedure currently in scope. A yellow arrow indicates the next statement to be executed. Red bullets mark statements before which breakpoints have been set.

Parameter Variables Window

The parameter variables window displays the name, value, and type of each input and output variable for the current procedure. The values displayed here change as a running procedure sets or modifies its variables.

Parameter variables may be manually modified at any time while a procedure executes. Click a value to change and type the replacement text.

Local Variables Window

The behavior of the local variables window is analogous to that of the parameter variables window. By default, this window contains only variables defined within the current scope.

While executing within a nested procedure call, it is still possible to access the scope of higher level procedures. Click on any of the procedure names listed in the callstack window to view or modify elements of calling procedures.

Callstack Window

The callstack window shows a list of active, or uncompleted, procedure calls. The top entry in the list is called the current procedure. This procedure determines the scope of variables in the local and parameter variable windows.

The callstack window helps trace the operation of a stored procedure as it executes a series of nested procedure calls. For example, a first procedure can call a second procedure, which in turn can call a third procedure. Nested calls such as these can complicate the debugging process. The callstack window keeps track of past calls and the current procedure.

The currently executing procedure can be identified by the presence of a yellow arrow in source code window. In order to view the point from which the parent procedure called the current one, click on the name listed below the current procedure in the callstack window. The source code window will then display the parent procedure's source along with a green arrow pointing to the line of code that called the current procedure.

Results Text Window

The results text window is used to display any output from your stored procedure. This area is also used to display server error messages.

Status Bar

The status bar, located at the bottom of the debugger's interface, contains read-only information about the current session. It lists the current state of execution (running, completed, or aborted), which host the debugger is connected to, which user it is logged on as, and the position of the cursor within the source code window.


SQL Query Analyzer Dialog Boxes and Windows

This section provides information about SQL Query Analyzer dialog boxes:

  • Connect to SQL Server

  • Create New Index

  • Create Statistics

  • Current Connection Properties

  • Customize

  • Customize Toolbar

  • Edit Existing Index

  • Edit Transact-SQL Script

  • Execute Procedure

  • Manage Indexes

  • Manage Statistics

  • New

  • Object Search

  • Options

  • Select Database

  • T-SQL Debugger

  • Template Parameter Value

  • Update Statistics

  • Window Selector


Connect to SQL Server Dialog Box

Use the Connect to SQL Server dialog box to connect to a Microsoft® SQL Server™ database.

Options

SQL Server

Specify the name of an instance of SQL Server to which you want to connect. In Microsoft Windows NT®, you must have the required system permissions and rights to start SQL Server either locally or remotely. On computers running the Microsoft Windows® 95 or Windows 98 operating system, every user can start SQL Server locally. The operating system prevents SQL Server from being started remotely.

Start SQL Server if it is stopped

Start an instance of SQL Server if it is stopped.

(...)

Display the list of active SQL Server database servers on the network. This list only displays instances of SQL Server on Windows NT. This functionality is not available on computers running the Microsoft Windows 95 or Windows 98 operating system.

Connect using:

Specify the type of authentication to use when connecting to the database server.

Windows NT authentication | Specify that SQL Server will use the Windows NT user information to validate the user. This option is only available when connecting to an instance of SQL Server on Windows NT. The client needs to be part of a Windows NT domain or workgroup. The user needs to be validated as a Windows NT user before access is granted.
SQL Server authentication | Specify the use of standard SQL Server security validation. This is the default and only available option for an instance of SQL Server on the Windows 95 or Windows 98 operating system. It is optional for an instance of SQL Server on Windows NT. The login must be added to the SQL Server before a user can log in.
Login name | Specify a login recognized in SQL Server.
Password | Specify the password for the login name.


Create Missing Statistics Dialog Box

Use the Create Missing Statistics dialog box to create missing statistics. SQL Query Analyzer suggests additional indexes and statistics on nonindexed columns that would improve the query optimizer's ability to process a query. In particular, SQL Query Analyzer displays missing statistics so that they can be created.

Options

Statistics name

View the name of the statistic missing from the selected column.

Column name

View the column missing the specified statistics.

Column type

View the type of column missing the specified statistics.

Amount of data to sample

Specify how much data to sample for the statistic.

Default | Select to sample the default amount of data for the selected statistic.
Sample all the data | Specify to sample all of the data for the selected statistic.
Sample % of the data | Specify the percentage of the data to sample for the selected statistic.

Do not automatically recompute statistics

Specify to not automatically recompute statistics. This is not recommended.

Edit SQL

Open the Edit Transact-SQL Script dialog box to edit the CREATE STATISTICS statement.


Create Statistics Dialog Box

Use the Create Statistics dialog box to define statistics on the database and table specified in the Manage Statistics dialog box.

Options

Name

Specify the name of the statistic to create.

Column

Specify the columns to include in the new statistic.

Data type

View the data type of the column.

Amount of data to sample

Specify how much data to sample for the statistic.

Default | Select to sample the default amount of data for the statistic.
Sample % of the data | Specify the percentage of the data to sample for the statistic.
Sample all the data | Specify to sample all of the data for the statistic.

Do not automatically recompute statistics

Specify to not automatically recompute statistics. This is not recommended.

Edit SQL

Open the Edit Transact-SQL Script dialog box to edit the CREATE STATISTICS statement.


Current Connection Properties Dialog box

Use the Current Connection Options dialog box to specify the settings for the current connection.

Note To specify the connection properties for all new connections, use the Connection Properties tab on the Options dialog box.

Options

Set nocount

Suppress the rowcount message. This setting reflects the SET NOCOUNT ON/OFF state set in a query or stored procedure. For more information, see SET NOCOUNT in SQL Server Books Online. The default setting is OFF.

Set noexec

Compile, but do not execute the statements. This setting is useful for validating syntax and object names. It is also useful for debugging statements that may be part of a larger batch of statements. The default setting is OFF.

Set parseonly

Parse the syntax, but do not compile or execute the statements. This option does not validate object names. The default for this setting is OFF.

Set concat_null_yields_null

Return NULL if any operand in a concatenation operation is NULL.

Set rowcount

Return the specified number of rows and stop the query. The default (0) returns all rows.

Set arithabort

Terminate a query when an overflow or divide-by-zero error occurs during query execution.

Set showplan_text

Show execution information, but do not execute.

Set statistics time

Show the number of milliseconds required to parse, compile, and execute statements. The default setting is OFF.

Set statistics IO

Show information about disk activity. The information includes the name of the table used, the number of scans performed, the number of pages read from the data cache and the disk, and the number of pages placed into the cache for the query. The default setting is OFF.

Show stats I/O

Specify that the information regarding disk activity generated by each query statement be shown.

Set ansi_defaults

Set the options in this group to comply with SQL-92.

Set ansi nulls | Return UNKNOWN if a comparison operator has any null expressions. This option sets the ANSI_NULLS option to ON. The ANSI syntax "IS NULL" or "IS NOT NULL" must be used for all null comparisons. If the ANSI_NULLS option is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL.
Set ansi_null_dflt_on | Overrides the default nullability for new columns.
Set ansi_padding | Do not automatically truncate trailing blanks and trailing zeros. This option sets the ANSI_PADDING option to ON. Trailing blanks on varchar values and trailing zeroes on varbinary values are not truncated automatically.
Set ansi_warnings | Issue warnings on certain error conditions. This option sets the ANSI_WARNINGS option to ON. SQL Server issues warning messages for conditions that violate ANSI rules but do not violate the rules of Transact-SQL.
Set cursor_close_on_commit | Close cursors when a transaction commits.
Set implicit_transactions | Begin transactions automatically, without explicit BEGIN TRANSACTION statements.
Set quoted_identifier | Interpret characters enclosed in double quotation marks as identifiers. This option sets the QUOTED_IDENTIFIERS option to ON when the SQL Server ODBC driver connects. The default for this setting is OFF. For more information, see SET QUOTED_IDENTIFIERS.

Reset All

Restore all connection properties to their initial settings.


Customize Dialog Box

Use the tabs on this dialog box to define keyboard shortcuts and add commands to the Tools menu.

  • Custom Tab, Customize Dialog Box

  • Tools Tab, Customize Dialog Box


Custom Tab, Customize Dialog Box

Use the Custom tab of the Customize dialog box to define up to twelve keyboard shortcuts for stored procedures.

Options

Shortcut

Is the keystroke combination used to run a stored procedure or execute a SQL statement.

Stored Procedure

Specify the stored procedure or SQL statement to run when the shortcuts are used. sp_help, sp_who, and sp_lock are defined by default.

Reset All

Removes all modifiable shortcuts.


Tools Tab, Customize Dialog Box

Use the Tools tab of the Customize dialog box to add commands to the Tools menu.

Options

Menu contents

Specify the text that is to appear on the Tools menu. This might be the name of an executable or the name of a document to be opened.

Add Menu Item button

Add the selected item to the Tools menu.

Remove Menu Item button

Remove the selected item from the Tools menu.

Up arrow and Down arrow

Change the position of the selected item on the Tools menu.

Command

Specify the name and location of the file to be executed. For example, to execute osql, enter:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe

Arguments

Specify any arguments for the command. For example, if the command is osql, you might specify the options -Usa -P.

Initial directory

Specify the location from which the command will be executed.


Customize Toolbar Dialog Box

Use the Customize Toolbar dialog box to specify the buttons that appear on the toolbar.

Available toolbar buttons

List the buttons that can be placed on the toolbar.

Current toolbar buttons

List the buttons on the toolbar.

Add

Move the selected button in the Available toolbar buttons list to the Current toolbar buttons list.

Remove

Move the selected button in the Current toolbar buttons list back to the Available toolbar buttons list.

Reset

Restore the toolbar to its initial state.


Edit Transact-SQL Script Dialog Box

Use the Edit Transact-SQL Script dialog box to edit the SQL statements generated by the index management and statistics management features.

Options

Parse

Parses, but does not execute, the SQL in the text box.

Execute

Executes the SQL in the text box.


Debug Procedure Dialog Box

Use the Debug Procedure dialog box to specify parameter values and debug a stored procedure.

Options

Procedures

Select the stored procedure to be debugged.

Parameters

List the parameters defined for the stored procedure.

Data type

Show the data type of the selected parameter.

Direction

Show whether the selected parameter is an input or output parameter.

Value

Specify a value for the selected parameter.

Set to null

Specify a null value for the selected parameter.

Auto roll back

Automatically rolls back all work performed during execution of the procedure.

Execute

Debug the stored procedure using the specified values.


Execute Procedure Dialog Box

Use the Execute Procedure dialog box to specify parameter values and execute a stored procedure.

Options

Procedures

Select the stored procedure to be executed.

Parameters

List the parameters defined for the stored procedure.

Data type

Show the data type of the selected parameter.

Direction

Show whether the selected parameter is an input or output parameter.

Value

Specify a value for the selected parameter.

Set to null

Specify a null value for the selected parameter.

Execute

Execute the stored procedure using the specified values.

Copy

Using the specified values, produce the Transact-SQL statements used to invoke the stored procedure, and copy those statements to the clipboard.


Extended Property Dialog Box

Use the Extended Property dialog box to define extended properties on objects in the database. These extended properties can be used to store application-specific or site-specific information about the objects.

Options

Object name

Is the name of the object.

Name

Specify the name of a property.

Value

Specify the value for a property.

Add button

Add a property to the list.

Remove button

Remove a property from the list.

Apply

Apply the displayed extended properties to the object.

Refresh

Discard unapplied properties and values.

See Also

Using Extended Properties on Database Objects


Index Dialog Box

Use the Create New Index and Edit Existing Index dialog boxes to define and modify indexes on the database and table specified in the Manage Indexes dialog box.

Options

Index name

Specify the name of the index to create or edit.

Column

Specify or change the columns in the selected index.

Sort Order

Specify or change the sort order for the column. The default order is descending.

Data type

View the data type of the column.

Length

Display the length of the selected column.

Nullable

Display whether the selected column is nullable.

Precision

Display the precision of the selected column.

Scale

Display the scale of the selected column.

Change column order

Change the position of the column in the index.

Move Up | Move the selected column up one.
Move Down | Move the selected column down one.

Index options

Specify the type of index and other index options.

Clustered index | Specify a clustered index.
Unique values | Specify to index the columns by unique values.
Ignore duplicate values | Specify to ignore duplicate values.
Do not recompute statistics (not recommended) | Specify to not recompute the statistics of the index. This is not recommended.
File group | Specify the filegroup of the index.
Pad index | Specify to pad the index. Padding the index leaves space on each interior node of the index.
Drop existing | Specify to delete any existing index of the same name before creating the new index.
Fill factor | Specify how full SQL Server should make the leaf level of each index page during index creation.

Edit SQL

Open the Edit Transact-SQL Script dialog box where you can edit the CREATE INDEX or ALTER INDEX statement.


Manage Indexes Dialog Box

Use the Manage Indexes dialog box to select the database and table or view for which you want to create, edit, or delete indexes.

Options

Database

Specify the database.

Table/view

Specify the table or view for the index.

Include system objects

Specify to include system objects in the list of tables and views.

Index

Specify an existing index in the selected database and table or view.

Clustered

Indicates whether the existing index is clustered.

Columns

Indicates the columns on which the index is based.

New

Open the Create New Index dialog box to define a new index on the selected database and table.

Edit

Edit the selected index in the Edit Existing Index dialog box.

Delete

Drop the selected index on the selected database and table.


Manage Statistics Dialog Box

Use the Manage Statistics dialog box to select the database and table or view for which you want to create, edit, or delete statistics.

Options

Database

Specify the database.

Table/view

Specify the table or view for which to create, edit, or delete statistics.

Include system objects

Specify to include system objects in the list of tables and views.

Name

Lists the names of existing statistics.

Columns

Shows the columns on which existing statistics are based.

New

Open the Create Statistics dialog box to define a new statistic.

Update

Update the selected statistic in the Update Statistics dialog box.

Delete

Delete the selected statistic.


New Dialog Box

Use the General tab of the New dialog box to open a template or a blank Query window. Use the options to control the display of template folders and files in the dialog box.

Options

Up One Level

Display the contents of the folder above the current level.

Large Icons

Display the template folders and files as large icons.

List

Display the template folders and files in a multi-column list.

Details

Display the template folders and files in a list that provides details such as date modified and size.


Object Search Dialog Box

Use the Object Search dialog box to locate objects in the current database server.

Options

Object name

Specify the name of the object to find.

Case sensitive

Specify a case-sensitive search, regardless of the case-sensitive setting of the database server.

Database

Specify the database to be searched or specify all databases.

Hit limit

Specify the maximum number of items to return.

All object types

Consider all object types when performing the search.

System table

Search for system tables.

User table

Search for user tables.

View

Search for views.

Stored procedure

Search for stored procedures.

User defined function

Search for user defined functions.

Column

Search for columns.

Index

Search for indexes.

Trigger

Search for triggers.

Extended procedure

Search for extended stored procedures.

DRI constraint

Search for declarative referential integrity constraints.

Extended property search

Search for extended properties.

Property name | Specify the name of the property to find.
Property value | Specify the property value to find.

Find Now

Begin the search operation. The search results are displayed in the lower part of the window.

Note Right-click the results to display the Context Menu, which enables you to open the selected object in the Open Table window, or perform various editing and scripting activities.

New Search

Clear the selected items so new search criteria can be specified.


Options Dialog Box

Use the tabs on the Options dialog box to specify file locations and other configuration settings, configure the Editor pane and the Results pane, specify general connection options, specify SET options, and specify the fonts used.

  • General Tab, Options Dialog Box

  • Editor Tab, Options Dialog Box

  • Results Tab, Options Dialog Box

  • Connections Tab, Options Dialog Box

  • Connection Properties Tab, Options Dialog Box

  • Fonts Tab, Options Dialog Box

  • Scripting Tab, Options Dialog Box


General Tab, Options Dialog Box

Use the General tab on the Options dialog box to specify file locations, file extensions, and other configuration settings.

Options

Query file directory

Specify the default location for query files.

Result file directory

Specify the default location for results files.

Template file directory

Specify the default location for template files.

Query file extension

Specify the default extension for query files.

Results file extension

Specify the default extension for results files.

Template file extension

Specify the default extension for template files.

Mark SQL Query Analyzer as the default editor for query file extensions

Specify that SQL Query Analyzer is the default editor for query files.

Do not prompt for unsaved files on closure

Specify that files will be closed without asking whether they should be saved.

Load

Load a file containing configuration settings.

Save

Save current configuration file settings for backup purposes or for transfer to another computer.


Editor Tab, Options Dialog Box

Use the Editor tab on the Options dialog box to configure the Editor pane.

Options

Number of Undo buffers

Specify the number of undo operations that can be performed.

Max Undo buffer size

Specify the total size of all undo buffers.

Undo buffer limit handling

Specify how undo buffer limitations are handled.

Tab size (in spaces)

Specify the number of spaces to indent when the tab key is used.

Save tabs as spaces

Specify whether tabs are saved as spaces or as tab characters.

Disable dragging text within editor

Control whether text can be dragged in the Editor pane.

Default (non-Unicode) file open format

Specify the default file format.

Tabbed mode (vs. Split mode)

Specify whether query results are displayed on separate tabs (Tabbed mode) or all in the lower half of the Query window.

Switch to Results tab after query executes

Specify whether the Results tab is displayed automatically when a query is executed. If this is not enabled, you must select the Results tab manually.

Show tabs on top

Specify whether the window tabs are displayed at the top or bottom of the window.


Results Tab, Options Dialog Box

Use the Results tab on the Options dialog box to configure the Results pane. An asterisk (*) indicates that the option does not apply to the results to grid feature.

Options

Default results target

Specify the default type of results output.

Result output format

Specify the format for the results: Column Aligned, Comma Separated (CSV), Tab Delimited, Space Delimited, Other Delimiter.

Delimiter

Specify the delimiter character if Other Delimited is selected.

Maximum characters per column (*)

Specify the maximum number of characters returned in an output column. This setting also affects the behavior of the Transact-SQL Debugger window.

Print column headers

Specify whether to include column headings in the output.

Output query

Specify that the first three lines of a query appear in the Results pane. The default for this setting is OFF.

Discard results

Specify that query results are discarded.

Scroll results as received (*)

Specify that the results should be scrolled as they are returned.

Right-align numeric (*)

Specify that numbers should be right-aligned.

When a query batch completes:

Specify that a signal should be given when a query batch finishes executing.

Play the Windows message beep

Specify that the Windows message beeps when the batch completes.

Play this WAV file

Specify that a .wav file sounds when the batch completes.


Connections Tab, Options Dialog Box

Use the Connections tab on the Options dialog box to configure general connection settings.

Options

Login timeout (seconds)

Specify the amount of time to wait for a connection before timing out. The connection timeout applies only after Query Analyzer has searched every computer on the network for the requested server.

Network package size (bytes)

Specify the network package size.

Query timeout (seconds)

Specify the amount of time a query can process before timing out.

Lock timeout (milliseconds.)

Specify the amount of time to wait for a lock before timing out.

Batch separator

Specify the word or character that can be used to separate batches.

Parse ODBC message prefixes

Specify that ODBC prefixes should be parsed.

Perform translation for character data

Specify that translation should be performed for character data.

Use regional settings when displaying currency, number, dates and times

Specify that regional settings should be used for currency, numbers, dates, and times.

Disconnect after query executes

Specify that the connection should be dropped after the query executes.

Parse ODBC message prefixes

Specify to parse ODBC message prefixes. When this option is selected the prefix [Microsoft][ODBC SQL Server Driver][SQL Server] is removed from the ODBC error messages. If the option is OFF, the error messages are presented. The default for this setting is ON.

Note This option works only when connected to an instance of SQL Server version 4.21a or later.

T-SQL batch separator

Specify the Transact-SQL command to separate batches. The default for this setting is GO.

Note This option works only when connected to an instance of SQL Server 4.21a or later.

Query time-out (seconds)

Specify the number of seconds to wait for any request on the connection to complete before returning to the application. When this time is exceeded, the client prompts the user to continue waiting. The default is 0, which indicates an infinite wait or no time-out.

Note This option works only when connected to an instance of SQL Server 4.21a or later.

Change the language of SQL Server system messages to

Specify the language to use for system messages sent from SQL Server to the client. The list presented is the actual list of possible languages present on a given server. However, the extensive list of languages is only available when connecting to a Microsoft® SQL Server™ version 7.0 or later. SQL Server version 6.5 and earlier support only English, French, German, and Japanese.

Note This option works only when connected to an instance of SQL Server 4.21a or later.

Perform translation for character data

Specify to perform translations for character data. Turning on this setting causes the ODBC driver to translate characters between the client and server code pages as data is exchanged. The attribute only affects data stored in SQL Server char, varchar, and text columns. When the translate option is OFF, the driver does not translate characters from one code page to another in character data exchanged between the client and the server. The driver configures the character translation automatically, determining the code page installed on the server in use by the client. The default for this setting is ON.

Note This option works only when connected to an instance of SQL Server 4.21a or later.

Use regional settings when outputting currency, number, dates, and times

Specify to use regional settings when converting numeric, date, time, and currency data to a string. Turning this setting to ON causes the ODBC driver to respect the local client setting when converting numeric, date, time, and currency values to character strings. The conversion is from SQL Server native data types to character strings only. When the setting is OFF, the driver does not convert numeric, date, time, and currency data to character string data using the client locale setting. The conversion setting is only applicable to output conversion and is only visible when currency, numeric, date, or time values are converted to character strings (which is always the case with Query Analyzer). The default for this setting is OFF.

Note This option works only when connected to an instance of SQL Server 4.21a or later.


Connection Properties Tab, Options Dialog Box

Use the Connection Properties tab on the Options dialog box to specify SET options for all new connections.

Note The options on the Connection Properties tab of the Options dialog box are the same as those on the Current Connection Properties dialog box. However, connection properties set in the Options dialog box affect new connections. Options set in the Current Connection Properties dialog box affect only the current connection.

Options

Set nocount

Suppress the rowcount message. This setting reflects the SET NOCOUNT ON/OFF state set in a query or stored procedure. For more information, see SET NOCOUNT in SQL Server Books Online. The default setting is OFF.

Set noexec

Compile, but do not execute, the statements. This setting is useful for validating syntax and object names. It is also useful for debugging statements that may be part of a larger batch of statements. The default setting is OFF.

Set parseonly

Parse the syntax but do not compile or execute the statements. This option does not validate object names. The default for this setting is OFF.

Set concat_null_yields_null

Return NULL if any operand in a concatenation operation is NULL.

Set rowcount

Return the specified number of rows and stops the query. The default (0) returns all rows.

Set arithabort

Terminate a query when an overflow or divide-by-zero error occurs during query execution.

Set showplan_text

Show execution information but do not execute.

Set statistics time

Show the number of milliseconds required to parse, compile, and execute statements. The default setting is OFF.

Set statistics IO

Show information about disk activity. The information includes the name of the table being used, the number of scans performed, the number of pages read from the data cache and the disk, and the number of pages placed into the cache for the query. The default setting is OFF.

Show stats I/O

Specify that the information regarding disk activity generated by each query statement be shown.

Set ansi_defaults

Set the options in this group to comply with SQL-92.

Set ansi nulls | Return UNKNOWN if a comparison operator has any null expressions. This option sets the ANSI_NULLS option to ON. The ANSI syntax "IS NULL" or "IS NOT NULL" must be used for all null comparisons. If the ANSI_NULLS option is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL.
Set ansi_null_dflt_on | Overrides the default nullability for new columns.
Set ansi_padding | Do not automatically truncate trailing blanks and trailing zeros. This option sets the ANSI_PADDING option to ON. Trailing blanks on varchar values and trailing zeroes on varbinary values are not truncated automatically.
Set ansi_warnings | Issue warnings on certain error conditions. This option sets the ANSI_WARNINGS option to ON. SQL Server issues warning messages for conditions that violate ANSI rules but do no violate the rules of Transact-SQL.
Set cursor_close_on_commit | Close cursors when a transaction commits.
Set implicit_transactions | Begin transactions automatically, without explicit BEGIN TRANSACTION statements.
Set quoted_identifier | Interpret characters enclosed in double quotation marks as identifiers. This option sets the QUOTED_IDENTIFIERS option to ON when the SQL Server ODBC driver connects. The default for this setting is OFF. For more information, see SET QUOTED_IDENTIFIERS in SQL Server Books Online.

Reset All

Restore all connection properties to their initial settings.


Fonts Tab, Options Dialog Box

Use the Fonts tab on the Options dialog box to control the fonts used on SQL Query Analyzer elements. These elements include:

  • Editor

  • Results Text

  • Results Grid/Open Table

  • Showplan

  • Statement Profile

  • Client Statistics

  • Object Browser

You can specify a font and size to be used for all components in SQL Query Analyzer. In addition, you can specify different colors for the various types of text that appear in the Editor pane.

Options

Text (Query characters)

Specify the format of alphabetic text.

Text Selection (Query characters)

Specify the format of query text when it is selected.

Keyword (Query characters)

Specify the format of Transact-SQL keywords.

Stored Procedure (Query characters)

Specify the format of stored procedures.

System table (Query characters)

Specify the format of system tables.

Global variable (Query characters)

Specify the format of global variables.

Comment (Query characters)

Specify the format of comments within a Transact-SQL script.

Number (Query characters)

Specify the format of numeric text.

String (Query characters)

Specify the format of alphanumeric text contained within single quotation marks.

Operator (Query characters)

Specify the format of symbols used to perform mathematical computations or comparisons between columns or variables. Operators are classified as Arithmetic, Bitwise, Comparison, or Join.

Foreground

Specify the foreground character color for the content type selected in the Color box.

Background

Specify the background color for the content type selected in the Color box.

Font

Specify the font for the content type selected in the Color box.

Size

Specify the point size of the content type selected in the Color box.

Sample

Display the query characters in the selected format.

Reset All

Reset all options to their original default values.


Scripting Tab, Options Dialog Box

In the object browser, you can script the creation of objects. Use the Scripting tab on the Options dialog box to set scripting options and tailor scripted output. Available options include:

Script Formatting Options

Include descriptive headers in the script.

Adds a /*...*/ delimited comment to the scripted output. The comment includes the full name of the object, and the creation time of the script.

Prefix the script with a check for existence. When script is executed, component is created only if it does not exist.

Adds statements to the script that check for the object's existence. If the object exists, the script does not attempt to create it.

Identifier delimiter

Allows you to tailor the format of scripted identifiers for compatibility with Microsoft® SQL Server™ version 6.5, SQL Server 7.0 or SQL Server 2000. Options include:

  • Version dependent defaults

  • None

  • [] -- 7.0 compatible quoted identifier

  • "" -- 6.5 compatible quoted identifier

Do not use a command terminator.

Omits the 'GO' terminator from scripted output. If this option is not selected, each Transact-SQL statement will be terminated with a 'GO' command.

Only script 7.0 compatible features.

Only include SQL Server version 7.0 compatible options in scripted output.

Do not script the collation clause if source is an instance of SQL Server version 7.0 or later.

Do not script the collation clause if source is an instance of SQL Server version 7.0 or later.

Generate Transact-SQL to remove referenced component. Script tests for existence prior to attempt to remove component.

Adds statements to the script that test for existence of the component and conditionally remove the component before re-creating it.

Include extended property scripting as part of object scripting.

If the object has extended properties associated with it, the appropriate calls to sp_addextendedproperty will be appended to the end of the script to restore any previously associated extended properties.

Do not include 'ON <filegroup>' clause directing filegroup use.

Do not include 'ON <filegroup>' clause directing filegroup use.

Script object-level permissions.

Adds statements to the script that restores any special privileges associated with the object.

Table Scripting Options

Qualify object name by its owner.

Adds current owner name to object name syntax. (for example, syscomments becomes dbo.syscomments).

Convert specification of user-defined data types to the appropriate SQL Server base data type.

Converts user-defined data types to the SQL Server supplied data types (for example, decimal, int, char, varchar) on which the user-defined data types are based.

Do not include any clauses defining declarative referential integrity constraints.

Do not include any clauses defining declarative referential integrity constraints in the scripted output.

Do not include definition of identity property, seed, and increment.

Removes the IDENTITY property from column definitions in the table creation command. For more information, see IDENTITY (Property).

Generate SET ANSI_PADDING ON and SET ANSI_PADDING OFF statements before CREATE TABLE statements.

Prefixes the table creation command with a SET ANSI_PADDING statement. For more information, see SET ANSI_PADDING.


Select Database Dialog Box

Use the Select Database dialog box to specify the database context for the active Query window. The dialog box provides a list of the databases in the current database server.

Options

Name

Show the name of the database. The current database appears in blue.

Compatibility Level

Identifies the current compatibility level of the database.

Status

Show the current state of the database, such as whether the database is offline or in single-user mode. If this column is empty, the database is in normal operating mode.

Refresh

Refresh the list.


Replace Template Parameters Dialog Box

Use the Replace Template Parameters dialog box to specify values for parameters in stored procedures or functions.

Options

Parameter

List the parameters in a stored procedure or function.

Type

Identify the data type of the template parameters.

Value

Specify a value for the selected parameter.

Replace All

Insert the specified parameter values into the code in the Editor pane.


Update Statistics Dialog Box

Use the Update Statistics dialog box modify statistics on the database and table specified in the Manage Statistics dialog box.

Options

Name

View the name of the statistic to update.

Columns

View the columns to include in the selected statistic.

Amount of data to sample

Specify how much data to sample for the statistic.

Default | Select to sample the default amount of data for the statistic.
Sample % of the data | Specify the percentage of the data to sample for the statistic.
Sample all the data | Specify to sample all of the data for the statistic.
Sample rows | Specify the number of rows to sample for the selected statistic.

Update statistics options

Specify additional statistics options.

Include columns | Specify to include columns in the selected statistic.
Do not automatically recompute statistics | Specify to not automatically recompute statistics. This is not recommended.

Edit SQL

Open the Edit Transact-SQL Script dialog box to edit the UPDATE STATISTICS statement.


Window Selector Dialog Box

Use the Window Selector dialog box to navigate windows in SQL Query Analyzer.

Options

Select window(s)

List the windows that are open in SQL Query Analyzer. The active window appears in blue. The list box displays this information about each window.

Column | Description
Window Type | The type of window, such as Query Window, Transact-SQL Debugger, or Object Search.
Server | Database server.
Database | Database context for the window.
User | Login that opened the window.
Description | Information about the window. Information includes the name of an unsaved window, the name and path of a saved window, and the name of a stored procedure in the Transact-SQL Debugger.

Activate

Make the selected window the active window and close the dialog box.

Close Window(s)

Close the selected windows.

Save

Save the contents of the selected window to a file.


Transact-SQL Debugger

The Transact-SQL Debugger window displays the text of the stored procedure to be debugged and provides the following debugging options.

Option | Description
Go | Runs the stored procedure in debugging mode.
Toggle Breakpoint | Sets or removes a breakpoint at the current line. You cannot set a breakpoint on lines containing nonexecutable code such as comments, declaration statements, or blank lines.
Remove All Breakpoints | Clears all breakpoints in your code.
Step Into | Executes one code statement at a time. Step Into executes the statement at the current execution point. If the statement is a call to a procedure, the next statement displayed is the first statement in the procedure.
Step Over | Executes one code statement at a time. If the current statement contains a call to a procedure, Step Over executes the procedure as a unit, and then steps to the next statement in the current procedure. Therefore, the next statement displayed is the next statement in the current procedure regardless of whether the current statement is a call to another procedure.
Step Out | Executes the remaining lines of a function in which the current execution point lies. The next statement displayed is the statement following the procedure call. All of the code is executed between the current and the final execution points.
Run to Cursor | Specifies a statement further down in your code where you want execution to stop. Use this option to avoid stepping through large loops.
Restart | Restarts execution from the beginning of the stored procedure.
Stop Debugging | Halts debugging.
Watch | Displays the current watch expressions.
Callstack | Lists the procedures calls that have started but are not completed.
Auto Rollback | Automatically rolls back all work performed during execution of the procedure.

The code pane displays the SQL statement you are debugging. Separate output panes are provided for local and global variables, for the result set of the query, and for the call stack. Note that global variable is a legacy term for the Transact-SQL built-in functions whose names start with @@.


Query Window Execution Plan Pane

The Execution Plan options graphically display the data retrieval methods chosen by the Microsoft® SQL Server™ query optimizer.

The Execution Plan pane uses icons to represent the execution of specific statements and queries in SQL Server rather than the tabular representation produced by the SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT statements.

Icons

Graphically Displaying the Execution Plan Using SQL Server Query Analyzer

Physical operators

Assert

Bookmark Lookup

Clustered Index Delete

Clustered Index Insert

Clustered Index Scan

Clustered Index Seek

Clustered Index Update

Collapse

Compute Scalar

Concatenation

Constant Scan

Deleted Scan

Filter (clsColumn)

Hash Match

Hash Match Root

Hash Match Team

Index Delete

Index Insert

Index Scan

Index Seek

Index Spool

Index Update

Inserted Scan

Log Row Scan

Merge Join

Nested Loops

Parallelism

Parameter Table Scan

Remote Delete

Remote Insert

Remote Query

Remote Scan

Remote Update

Row Count Spool

Sequence

Sort

Stream Aggregate

Table Delete

Table Insert

Table Scan

Table Spool

Table Update

Top

Cursor physical operator

Dynamic

Fetch Query

Keyset

Population Query

Refresh Query

Snapshot


Query Window Statistics Pane

The Show Client Statistics command provides detailed information about client-side statistics for execution of the query. The output result set displayed in the Statistics pane contains these columns.

Column name | Description
Counter | Counter label.
Value | The value of the counter, updated after each query execution .
Average | A running average from the beginning of the connection or since the last manual reset.

The three groups of statistics counters are:

  • Application profile

  • Network

  • Time

These statistics are displayed only when query results are generated. They are not displayed when commands such as Parse, Display Estimated Execution Plan, or Index Tuning Wizard are selected.

Application Profile Statistics
Counter | Description
Timer Resolution (milliseconds) | Minimum resolution of the server clock time in milliseconds. This is usually reported as zero and should only be considered if the number reported is large. If the minimum resolution of the server clock is larger than the likely interval for some of the timer-based statistics, those statistics could be inflated.
Number of INSERT, UPDATE, DELETE statements | Number of INSERT, UPDATE, or DELETE statements after SQL_PERF_START.
Rows affected by INSERT, UPDATE, DELETE statements | Number of rows affected by INSERT, UPDATE, or DELETE statements after SQL_PERF_START.
Number of SELECT statements | Number of SELECT statements processed after SQL_PERF_START.
Rows affected by SELECT statements | Number of rows selected after SQL_PERF_START.
Number of user transactions | Number of user transactions after SQL_PERF_START, including rollbacks. When an ODBC application is running with SQL_AUTOCOMMIT_ON, each command is considered a transaction.
Average fetch time | Equals SQLFetchTime/SQLFetchCount.
Cumulative fetch time | Cumulative amount of time it took to complete fetches against server cursors.
Number of fetches | Number of fetches performed against server cursors after SQL_PERF_START.
Number of open statement handles | Number of statement handles currently open on all connections open in the driver.
Max number of open statement handles | Maximum number of concurrently opened statement handles after SQL_PERF_START.
Cumulative number of statement handles | Number of statement handles opened after SQL_PERF_START.

Network Statistics
Counter | Description
Number of server roundtrips | Number of times the driver sent commands to the server and received a reply.
Number of TDS packets sent | Number of TDS packets sent to SQL Server by the driver after SQL_PERF_START. Large commands can take multiple buffers. For example, if a large command is sent to the server and it fills six packets, ServerRndTrips is incremented by one and BuffersSent is incremented by six.
Number of TDS packets received | Number of TDS packets received by the driver from SQL Server after the application started using the driver.
Number of bytes sent | Number of bytes of data sent to SQL Server in TDS packets after the application started using the driver.
Number of bytes received | Number of bytes of data in TDS packets received by the driver from SQL Server after the application started using the driver.

Time Statistics
Counter | Description
Cumulative client processing time | Cumulative amount of time the driver spent processing after SQL_PERF_START, including the time it spent waiting for replies from the server.
Cumulative wait time on server replies | Cumulative amount of time the driver spent waiting for replies from the server.


Query Window Trace Pane

The Show Server Trace command provides access to information used to determine the server-side impact of a query.

If enabled when a query is executed, the Show Server Trace command displays the Trace pane, which provides information about the event class, subclass, integer data, text data, database ID, duration, start time, reads and writes, and CPU usage.


Object Browser

Use Object Browser to navigate the database objects of the selected database server. Object Browser is accessible through a button on the toolbar and through the Object Browser command on the Tools menu.

The Objects tab contains a tree list that displays the databases in the selected database server. For each database, Object Browser lists these folders.

User Tables | For each user table, Object Browser lists columns, indexes, triggers, and dependencies.
System Tables | For each system table, Object Browser lists the columns, indexes, and dependencies.
Views | For each view, Object Browser lists the columns and dependencies.
Stored Procedures | For each stored procedure, Object Browser lists parameters and dependencies.
Extended Stored Procedures | For each extended stored procedure, Object Browser lists parameters and dependencies. Extended stored procedures are shown only for the master database.
Functions | For each user-defined function, Object Browser lists parameters and dependencies.

In addition to databases, the tree list displays a folder named Common Objects, which contains Transact-SQL built-in functions and base data types.

The Templates tab displays the templates stored in the Query Analyzer Templates directory.