SQL Worksheet
View SQL Worksheet page
The SQL Worksheet tab of the Preferences window allows users to determine if the commands are clear upon execution, the batch separator, the buffer size for the result set, and to specify how the results sets are displayed.
Execution
Clear commands upon execution (Default = checked)
Specify to clear the SQL statement or Transact-SQL code in the Editor pane after the command is successfully executed. If the execution is not successful, then the text remains in the Editor pane.
If this option in not selected, the text also remains in the Editor pane.
Note: After a successful execution, the text is always saved in the Command drop-down field at the top of the window.
Batch Separator (Default: GO)
Specify the separator used to signal the end of a batch of Transact-SQL statements.
Result buffer size
Unlimited or Fixed size (records)
Select Unlimited to return all records. Select Fixed size and then in the text field, set the maximum number of records to be returned. (Default fixed size: 1000)
Display results
Maximum characters per column: (Default: 256)
Specify how wide each column should be in the Resultn tab in the lower pane of the SQL Worksheet window.
Discard results: (Default: cleared)
Specify whether to show the results on the Resultn tab, or to just show the number of affected records on the Message Log tab.
Display results in
Grid or Text: Select between displaying the results in a grid or in plain text.
Under the Text option you can also select:
Output format (Default: Column aligned)
Select between Column aligned, Comma separated (CSV), Tab delimited, or Other delimiter.
Right aligned numerics: (Default: cleared)
When you select Column aligned for the Output format, specify whether or not to right align number columns.
Delimiter
When you select Other delimiter for the Output format, specify the character to be used as the delimiter between the columns.
Display headers:
Specify whether or not the column names are displayed.
SQL Scanner Tab
SQL Scanner
The SQL Scanner settings are used to define the requirements for the SQL Scanner module.
SQL Scanner Abstract Plan Settings
The settings in the SQL Classification tab are used by the SQL Scanner to help you identify which SQL statements are likely to be causing performance problems by classifying the SQL as Problematic, Complex, or Simple.
Options (SQL Scanner Tab)
Skip SQL within comments (Default = cleared)
Specify whether the scanning algorithm will ignore any SQL statements within comments enclosed by /* */, // and -- found in the source code. By default, the scanning algorithm will search for any SQL statements contained in comments.
Skip SQL that does not involve tables (Default = cleared)
Specify whether the scanning algorithm will ignore any SQL statements that do not involve tables.
Whole word matching for the first SQL keyword (Default = cleared)
Specify to search for SELECT, INSERT, UPDATE or DELETE as a whole word, the keyword must be preceded and followed by a space. The Scanner therefore will not find something like PROCEDUREINSERT and attempt to build a SQL statement from it.
Always use current database and user (Default = cleared)
Specify to always use the current database and user when the SQL Scanner is retrieving the query plan.
Automatically switch to current database and user when unable to set user to dbo
Specify to allow the SQL Scanner to switch the database and user for a job to the current database and current user when the login is unable to 'setuser dbo' during scanning. You will notice that the Job name is changed from "[xx] [dbo] yyy" to "[current db][current user] yyy" when the 'setuser dbo' fails. Also, in the information pane in the SQL Scanner window under "Connection Information," this line is added "Scanned using the current database and user setting".
When this option is not selected and the login is unable to ‘set user to dbo’, the SQL Scanner displays the ‘cannot set dbo’ status message in the Status column and it skips the Job.
Create Scanner Temp Table (Default = checked)
If selected, the scanning algorithm automatically creates a temporary table during scanning when a CREATE TABLE #TEMP or a SELECT INTO #TEMP statement is found. Tables created during the scanning process are dropped at the end of the process.
Include data (Default = cleared)
If selected, the SQL Scanner automatically executes any INSERT, DELETE, UPDATE, and SELECT INTO SQL statement that modifies a temporary table that is created by the SQL Scanner.
If this option is not selected, then a WHERE 0 =1 clause is added to the SELECT INTO statement so that no data is included when the temporary table is created.
Note: Selecting the Include data option may affect the total scanning time.
Override previous Scanner Temp Table (Default = checked)
If this option is selected, a temporary table is created with the first ‘CREATE TABLE #TEMP’ statement. If another ‘CREATE TABLE #TEMP’ with the same name is found, it will drop the current table and create a new one for every time if find a new ‘CREATE TABLE #TEMP’.
This option will not override any temporary tables created in User-Defined Temp Table window.
Override User-Defined Temp Table (Default = cleared)
If selected, a previous User-Defined Temp Table is overwritten if the SQL Scanner finds another create temporary table statement using the same table name. This option will not override any temporary tables created in the SQL Scanner.
Number of characters to be skipped at the beginning of every line for all files (Default = 0)
When a file is scanned, the SQL Scanner skips the number of characters specified at the beginning of every line.
Skip end of line continuation character (Default = <do not use>)
If the text of the SQL statement is on more than one physical line and there is a continuation character at the end of each physical line of the SQL text, then specify what the continuation character is so that it will be skipped. If this character is not skipped, it may cause the SQL Scanner to miss part of the SQL statement. Three options are included: <Do not use>, / (forward slash character), and _ (underscore character). In addition, you may add your own character. This character will be saved in the field as long as it is the selected character. Once you make another selection, your own character will not be remembered.