Query Analyzer is a fairly straightforward and simple tool to use. Since the user interface is user-friendly and easy to navigate, the purpose of this section of the chapter is not to provide a basic tutorial on using Query Analyzer, but to help you get more out of Query Analyzer by pointing out some of its more useful features that you might not be aware of.
When Query Analyzer first starts up, you are presented with a connection dialog as shown in Figure 6.1. Within this dialog, you can choose to connect using Windows authentication or using a SQL Server specific login ID and password. The SQL Server: field provides a drop-down list of the servers you've most recently connected to, or you can simply type in the name of the SQL Server. If you click on the button to the right of the server name box, it will display a list of SQL Servers that it detects are currently running on your network. If you have the appropriate administrative privileges, a check box is available that will allow you to automatically start the specified SQL Server if it is not running.
It is important to note that each editor window opened in Query Analyzer uses a separate connection to SQL Server. If you simply open a new editor window by pressing Ctrl+N, choosing New from the File menu, or by clicking on the New Query button on the toolbar, Query Analyzer will open the new query window using the connection information from the currently active query window. If you wish to open a new connection to a different SQL Server, choose the Connect option from the File menu or press Ctrl+O.
By default, the editor opens up in the default database specified for the login ID used to connect to SQL Server. You can change your database context via the drop-down menu on the toolbar, from the Change Database dialog selected from the Query menu, or by executing the use dbname command within the query window.
The main feature of Query Analyzer is its query editing capabilities. Query Analyzer comes with a full-featured T-SQL editor that provides color coding and highlighting of different types of syntax as well as an automatic help facility. The editor includes a standard text-editing feature set, which includes cut, copy and paste, a search and replace facility, and an Undo option (the number of Undo operations allowed is configurable in the Options dialog). It also provides the ability to go to a specific line in the editor, as well as the ability to set bookmarks. Bookmarks are useful if you are working with a large SQL script. You can toggle bookmarks on and off in the Edit menu or by pressing Ctrl+F2. You can then navigate to the next or previous bookmark by pressing F2 or Shift+F2 respectively, or by selecting Next Bookmark or Previous Bookmark from the Bookmark submenu in the Edit menu.
There are some additional features under the Advanced submenu in the Edit menu that are very useful for the developer, including the ability to convert text to upper- or lowercase, comment out or uncomment a block of text, or increase or decrease the indenting for a block of text. Highlight a section of text and then choose the option you want to apply from the menu or type the appropriate shortcut key sequence. For example, to comment out a highlighted section of text, press Ctrl+Shift+C.
The editor also provides the ability to open and save SQL files. The default file type extension for SQL scripts is .sql. The default file extension as well as the default directory for opening and saving files can be set in the General tab on the Options dialog, which is available under the Tools menu or by pressing Ctrl+Shift+O.
In addition to being able to edit your SQL files in Query Analyzer, you can also execute them. The SQL can be executed by choosing the Execute option on the Query menu, by clicking the green triangle in the toolbar, or by pressing F5 or Ctrl+E. you can execute the entire contents of the window, or highlight a section of text to be executed.
The results can be returned in either text format or in a spreadsheet-style grid format. The text format is most useful when executing a SQL script or stored procedure that generates print or raiserror messages that are intended to be interspersed with the resultsets, when you want to see the complete resultset fully listed and unformatted, or when you need to copy and paste the result into a text editor. Figure 6.2 shows an example of the text results for a query. Text results can be difficult to review if the columns are wide because the full column width is displayed.
When displaying results in grid format, each resultset is returned in a separate grid within the results pane. Grid format is most useful when you have resultsets that have wide columns. The columns in the result grid are initially sized to fit in the query window (if possible), and can be resized to any width. Each grid has its own set of scroll bars for scrolling through each individual resultset. Figure 6.3 shows an example of the grid results pane with multiple resultsets. Grid results are also useful if you want to save the results in a delimited text file or copy and paste the results into a spreadsheet. One downside of grid results is that the messages are displayed in a separate tabbed pane. You'll need to remember to check this pane when running a SQL script to make sure no error messages were generated.
You can switch between grid or text results by pressing Ctrl+D or Ctrl+T respectively, or by choosing the appropriate result option from the Query menu.
To save query results to a file, place the cursor in the results pane and choose the Save option from the File menu. If saving text results, the entire results pane is saved to a file in text format. You can choose to save the text as ANSI, Unicode, or OEM (code page 437). The default file type is *.rpt, but you can override this as well if you want to save the result as *.txt. You can set a different file extension for report files globally by changing it in the General tab of the Options dialog.
When saving grid results to a file, only the currently selected grid is saved to a file. In addition to saving the text as ANSI, Unicode, or OEM (code page 437), you also have the option of saving the text as a comma-or tab-delimited file. This is useful for generating files to be imported into another database or spreadsheet tool.
To close the results pane and increase the size of the edit pane, press Ctrl+R or choose the Close Results Pane option from the Window menu.
If you don't care for the split window mode of displaying the query and results, you can switch Query Analyzer to use a tabbed mode by checking the Tabbed mode (versus Splitter mode) option on the Editor tab in the Options dialog. In this mode, each pane is the full window height, and you select the pane you want to view by clicking on the appropriate tab displayed at the bottom of the edit window. You can also cycle through the panes in an edit window using the F6 key. An example of using Tabbed mode for an edit window is shown Figure 6.4.
Query Analyzer also provides the ability to send the results directly to a file rather than to the results pane. This option can be enabled by selecting the Results to File option from the Query menu, or by pressing Ctrl+Shift+F. Once this option is turned on for an edit window, the next time you execute a query for that window, a dialog box will come up, allowing you to specify the file you want the results saved to. All query results and messages will be sent to the file and a message such as the following will be displayed in the Messages pane:
The following file has been saved successfully: C:\Data\Reports\sp_help.rpt 86 bytes
Any error messages generated during the execution of the SQL commands will be sent to both the file and the Message pane. This feature is useful when your SQL script generates a large amount of output and you might be running Query Analyzer on a system with limited memory. Writing the result directly to a file avoids having to buffer the large resultset in memory, which could lead to paging and slow performance on the client workstation.
In addition to simply executing queries, the Query menu in Query Analyzer also provides the query analysis features from which Query Analyzer derives its name. There is an option to only parse the query without executing it. This is helpful when you have a large script file and you want to validate your SQL code for syntax errors before submitting it for execution.
Because SQL Server does not resolve table, column, or other object names until execution, running a parse check on your SQL code will only check for syntax errors. If the syntax is okay, but an invalid table name is specified, it will pass the parse check, but still fail during execution when the table name cannot be resolved.
The other query analysis options available include displaying the estimated execution plan, the actual execution plan, server trace info, and client statistics. For a detailed discussion on using these tools for query analysis, see Chapter 36, "Query Analysis."
Another great new feature in the Query Analyzer in SQL Server 2000 is the Object Browser. The Object Browser is a separate window pane that is usually displayed on the left side (you can move it to the right side by setting the option in the General tab in the Options dialog). If the Object Browser is not visible, you can invoke it by using the F8 key, or selecting the Show/Hide option from the Object Browser submenu in the Tools menu.
Note that when the Object Browser is open, it establishes a separate connection to SQL Server that is distinct from the connections made by the query windows. An Object Browser connection is made for each different server connection established in Query Analyzer. You can switch between connections to explore objects and databases on different servers by choosing the desired server from the drop-down list at the top of the Object Browser pane.
The Object Browser is very useful during development of your SQL code as a tool to display object names and object properties such as column names and datatypes, indexes, stored procedure parameters and datatypes, user-defined function arguments, and so on. The Object Browser also provides options for extracting the DDL for your database objects. It can generate code to create, alter, or drop the selected object. You can script the DDL to a new window, to a file, or to the Windows Clipboard by right-clicking on the object in the Object Browser and choosing the appropriate option (see Figure 6.5).
Query Analyzer provides a number of options for extracting the DDL for a database object. The script generated can automatically include the command to check for the existence of the object and automatically drop it before executing the CREATE command. You can also choose to include the commands to restore the existing permissions when the object is re-created.
The scripting options can be specified on the Script tab in the Options dialog box or by right-clicking on the object name in the Object Browser and choosing the Scripting Options menu option. Figure 6.6 shows the Scripting Options tab in the Options dialog.
Listing 6.1 shows a sample table-creation script generated by Query Analyzer for the Publishers table. The following options were enabled when this script was generated:
Include descriptive headers in the script.
Prefix the script with a check for existence. When script is executed, component is created only if it does not exist.
Generate Transact-SQL to remove referenced component. Script tests for existence before attempting to remove component.
Script object-level permissions.
Qualify object name by its owner.
/*** Object: Table [dbo].[publishers] Script Date: 9/17/2002 9:34:53 PM ****/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[publishers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[publishers] GO if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[publishers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[publishers] ( [pub_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [pub_name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__publisher__count__7D78A4E7] DEFAULT ('USA'), CONSTRAINT [UPKCL_pubind] PRIMARY KEY CLUSTERED ( [pub_id] ) ON [PRIMARY] , CHECK ([pub_id] = '1756' or ([pub_id] = '1622' or ([pub_id] = '0877' or ([pub_id] = '0736' or [pub_id] = '1389'))) or [pub_id] like '99[0-9][0-9]') ) ON [PRIMARY] END GO GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[publishers] TO [guest] GO
In addition to scripting the DDL for objects, the Object Browser can also script DML statements such as SELECT, INSERT, UPDATE, and DELETE for tables and views, and EXEC for stored procedures and user-defined functions. This is useful when building a SQL script and you need to include DML statements for database objects. It can save a lot of typing and ensures proper datatypes for columns and parameters. Listing 6.2 shows DML statements generated by Query Analyzer. Once generated, the DML statements will only need editing to add values for placeholders and to remove columns or add additional conditions.
SELECT [order_number], [customer_number], [order_date] FROM [pubs].[dbo].[orders] INSERT INTO [pubs].[dbo].[publishers] ([pub_id], [pub_name], [city], [state], [country]) VALUES(<pub_id,char(4),>, <pub_name,varchar(40),>, <city,varchar(20),>, <state,char(2),>, <country,varchar(30),>) UPDATE [pubs].[dbo].[jobs] SET [job_id]=<job_id,smallint,>, [job_desc]=<job_desc,varchar(50),>, [min_lvl]=<min_lvl,tinyint,>, [max_lvl]=<max_lvl,tinyint,> WHERE <Search conditions,,> DELETE FROM [pubs].[dbo].[stores] WHERE <Search conditions,,> DECLARE @RC int DECLARE @percentage int -- Set parameter values EXEC @RC = [pubs].[dbo].[byroyalty] @percentage
The DML statements generated from the Object Browser are actually templates. You can manually edit the template code and replace the template parameter values, or you can use the Replace Template Parameters option from the Edit menu. Choosing this option brings up a dialog that lists the template parameters in a table and lets you enter data values (see Figure 6.7). When you have entered the values for the desired parameters, click the Replace All button to substitute the supplied values back into the template.
Unfortunately, there is no option to script the object information to the current window. If you want to generate the DDL or DML into the current edit window, choose the option to script to the clipboard and then paste the code into the desired location in the SQL script.
One other feature of the Object Browser is the ability to view and edit the extended properties for database objects. Extended properties are like comments that can be stored in the system catalogs for database objects and can provide application-specific or site-specific information about the database objects. The uses of extended properties are only limited by your imagination. Some examples of uses for extended properties include:
Specifying a caption or description for a table, view, or column that applications can display in the user interface
Specifying an input mask for a column that applications can use to validate data before submitting Transact-SQL statements
Specifying formatting rules for displaying column data
The Design Table dialog in SQL Enterprise Manager only allows you to enter a column description that is stored as an extended property. Query Analyzer lets you create and view custom extended properties for databases, database objects, columns, and parameters. You can view, create, or edit extended properties by right-clicking on the item in the Object Browser and choosing the Extended properties option from the menu. This brings up the Extended Property dialog where you can view, add, or edit existing extended properties (see Figure 6.8).
In addition to the DML template code you can generate from the Object Browser, Query Analyzer also supports the use of template files for creating SQL scripts. Query Analyzer comes with a set of predefined template files for tasks such as creating a table, stored procedure, or database, attaching and detaching a database, and managing extended properties.
You can open a blank query window or populate a new query window with the code for one of the available templates by bringing up the drop-down list for the New Document icon on the toolbar and choosing the desired template, as shown in Figure 6.9. You can also load a template by choosing the New option from the File menu and navigating through the dialog to select the desired template file.
The code in Listing 6.3 shows a sample of the Create Table template for creating a table with an identity column.
-- ============================================= -- Create table with IDENTITY column -- ============================================= IF EXISTS (SELECT name FROM sysobjects WHERE name = N'<table_name, sysname, test_table>' AND type = 'U') DROP TABLE <table_name, sysname, test_table> GO create table <table_name, sysname, test_table> ( <column_1, sysname, c1> <datatype_for_column_1, , int> IDENTITY(<seed, , 100>, <increment, , 1>), <column_2, sysname, c2> <datatype_for_column_2, , int> NOT NULL) GO
You can edit the template manually, or, as with the DML templates, select the Replace Template Parameters option from the Edit menu, which will list all template parameters defined in the template. You can then enter the values for the template parameters, which can include table or column names, datatypes, stored procedure and function parameters, and so on. (Refer to Figure 6.7 for an example of this dialog.)
Templates provide a great facility for providing the basic syntax and parameters necessary for creating database objects. Templates can be used by developers to help enforce programming and database design standards. If the templates provided with SQL Server do not provide quite the structure or standards you desire, you can create your own template files. Template files are standard text files with a file extension of .tql. You can create them in any text editor, or create them with Query Analyzer and choose the Template SQL Files as the file type in the Save or Save As dialog.
While you can save your template files in any directory, you will have to open them manually by choosing the Template option from the drop-down list of the New File button in the toolbar. If you want your templates to be available in the menu or in the Template dialog, you need to save your templates in the default template directory. The SQL Server?provided templates are stored in the C:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer directory. If you want to create a new stored procedure template that appears in the drop-down list for the New File toolbar button, create the file in the Stored Procedure subfolder in this directory.
Template files contain only SQL code and template parameters. When defining your own templates, be sure to use descriptive parameter names so users can identify what values they are filling in when using the Replace Template Parameters dialog. The format of the parameters is as follows:
< parameter_name, [recommended_datatype], [default_parameter_value] >
The recommended_datatype and default_parameter_value are optional values. If not specified, you still need to include both commas in the parameter definition. Listing 6.4 provides a sample stored procedure template that contains a procedure header, standard transaction control statements, and error handling for an update procedure.
IF EXISTS (SELECT name FROM sysobjects WHERE name = N'<procedure_name, sysname, proc_test>' AND type = 'P') DROP PROCEDURE <procedure_name, sysname, proc_test> GO -- ============================================= -- Procedure name: <procedure_name, sysname, proc_test> -- Description: <procedure_description, , > -- Created By: <creator_name, , > -- Creation Date: <creation_date, , > -- Modified By: -- Modify Date: -- ============================================= CREATE PROCEDURE <procedure_name, sysname, proc_test> <@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>, <@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0> AS DECLARE @trncnt int, @err_message varchar(255) select @trncnt = @@TRANCOUNT if @trncnt = 0 -- if no transaction current active begin tran <procedure_name, sysname, proc_test> -- begin transaction else -- transaction active save tran <procedure_name, sysname, proc_test> -- set savepoint UPDATE <table_name, sysname, t1> set <col_1, , > = <@param2, sysname, @p2> where <search_condition, , > if @@error != 0 begin select @err_message = 'UPDATE of <@table_name, sysname, t1> failed' goto err_handler end if @trncnt = 0 -- if no transaction was active, need to commit begin tran begin commit tran <procedure_name, sysname, proc_test> return 0 end err_handler: rollback tran <procedure_name, sysname, proc_test> raiserror (@err_message, 16, 1) return -101 GO
Query Analyzer also provides a basic Object Search tool to help you find instances of an object in your SQL Server databases. This tool can be invoked from the Tools menu. This can be helpful if you want to locate all instances of a specific object in one or all of your databases. The object name can include the same SQL wildcards you would use in a LIKE clause. Figure 6.10 shows an example of using the Object Search tool to find all user tables, procedures, and columns that contain the word sales.
Within the list of search results, you can right-click on one of the rows and it will bring up the same dialog box as when you right-click on an object in the Object Browser, allowing you to script the object to a new window, file, or the clipboard, or to modify the extended properties of the object.
Query Analyzer provides up to 12 keyboard shortcuts for quickly executing stored procedures in Query Analyzer. SQL Server provides three predefined shortcuts:
For example, if you have a query window open and press Alt+F1, Query Analyzer will execute the sp_help command within the current query window context and display the results in the Results pane for that query window.
You can view or modify these shortcuts, or even add your own by selecting the Customize option from the Tools menu. On the Custom tab, you can enter the name of the stored procedure in the Stored Procedure column next to the desired keyboard shortcut you want to use to execute that stored procedure (see Figure 6.11). You might want to add your frequently executed procedures to the Custom menu to save you the trouble of having to type them every time you want to run them.
If you have a word, data value, or comma-separated list of values highlighted when you invoke a keyboard shortcut, Query Analyzer will pass the highlighted value(s) as parameters to the associated stored procedure.
The Customize option in the Tools menu in Query Analyzer also provides the ability to add your own custom options to the Tools menu. In the Customize dialog, click on the Tools tab. From here, you can enter commands for programs that can then be invoked from the Tools menu, as shown in Figure 6.12.
In the Command text box, you specify the full pathname of the command you want to invoke. You can click on the button to the right of the text box to browse to the command. In the Arguments text box, you specify any arguments to be passed to the command. If you click on the button to the right of the text box, you can specify variables to include in the argument string. The values of the variables are taken from the context of the currently active query window when you invoke the command from the Tools menu. The available argument variables are the following:
File Path?The full pathname of the file currently being edited in the query window
File Directory?The directory where the file currently being edited in the query window resides
File Name?The name (minus the path and file extension) of the file currently being edited in the query window
File Extension?The file extension of the file currently being edited in the query window
Current Directory?The current directory for Query Analyzer (this is typically the directory where a file was most recently opened or saved for that query window)
Server?The name of the SQL Server the query window is connected to
Database?The name of the current database the query window is using
User Name?The user name used to connect to SQL Server
Password?The password used to connect to SQL Server
The following is an example of the argument list using the built-in argument variables that is passed to a custom command that runs the currently open SQL file via the isql command:
[View full width]-U$(UserName) -P$(Password) -S$(Server) -d$(Database) -i$(FilePath) -o$(FileDir)\$( FileName).out -w1000
In the Initial Directory text box, you can set a default directory from which the command will be invoked.
Figure 6.13 shows the Tools menu with three custom commands added:
Run Script?This command passes the current file being edited in the query window to isql for execution. It outputs the results in a file in the same directory as the script file and the output filename is the same as the input file with the file extension changed to .out.
Send to Notepad?This command passes the current file being edited in the query window to Notepad.
SourceSafe?This command simply invokes the Visual SourceSafe application as a way to provide quicker access to the tool.
On the General tab of the Options dialog, you can save your Query Analyzer configuration to a file. This is especially useful if multiple people share the computer and have different preferences for how they like having Query Analyzer configured. Each person can save his or her configuration to file, and reload it to get everything back the way he or she likes it. It can also be useful if you prefer one configuration for SQL development and a different configuration for executing maintenance scripts. It's also not a bad idea to save your favorite configuration just in case you happen to really mess things up. Because the configuration is saved to a file (with file extension .sqc), you can even take your favorite configuration with you to another machine.