Statement execution

This section describes the following features:

Execute current statement

A specific SQL statement can be executed without selecting it. It is enough to place the mouse pointer in the statement and press Ctrl+K or Ctrl+E, and dbForge SQL Complete will define the statement boundaries itself.

Execute to cursor

This handy feature allows you to execute a script to the current position of the cursor.

To execute a statement to a cursor, position your cursor in the required place of the SQL Document and select the Execute To Cursor command from the SQL Complete menu.

Execute to cursor

If your statement is complex and contains multiple queries or T-SQL commands, the Execute To Cursor option will execute all statements above the cursor, including the statement where the cursor is placed. All statements that are below the cursor will not be executed.

Note

To better understand the Execute To Cursor option, study the following examples:

Example 1.

Execute to cursor example

In this worked example, the SELECT 1 and SELECT 2 queries will be executed. Please note, that the WHERE clause of the SELECT 2 query will also be executed. The SELECT 3 query won’t be executed.

Example 2.

Execute to cursor example

In this case, the SELECT 1, SELECT 2, and SELECT 3 queries will be executed.

Example 3.

Execute to cursor example

In this case, the SELECT 1 and SELECT 2 queries will be executed whereas the SELECT 3 query won’t be executed.

Multi-database execution

With SQL Complete for SQL Server, you can execute a script against multiple databases. To use the multi scripts feature, right-click anywhere in the SQL document and select Run On Multiple Targets.

Run On Multiple Targets

The Run On Multiple Targets wizard will open.

Run On Multiple Targets wizard

Here you need to:

1. Specify the database server connection.

Note

You can run a script against multiple databases within one execute operation only if the databases in question are located on the same database server. In case you need to execute a script against multiple databases that reside on different servers, you will have to do it in several steps by switching the connection in the wizard.

2. In the File Name field, provide a path to the script file or leave the field empty if you want to execute a script from the SSMS SQL document.

3. Select the databases against which you want to execute the script.

For convenience, you can hide the system databases from the list.

4. Select the Script Execution Mode:

Execute in series - SQL Complete executes the script against the first database in the list, after the execution is completed, the script will be executed against the next database in the list.

Execute in parallel - the script is executed against all the selected databases simultaneously.

SQL Query History

dbForge SQL Complete delivers a number of features that help optimize query performance. One of them is the SQL Query History. It stores main information about the executed SQL statements for a particular period. This helps the user to view, edit, and search the queries that were run on the database. Additionally, it possible to see who and when executed a query, as well as get other valuable information.

The SQL Query History information can come in handy for a number of cases. For instance, there might be a need to investigate a particular work case, check the backup history, or recover a specific query if your SQL Server suffers a crash.

This is what the SQL Query History window looks like.

SQL Query History

The SQL Query History grid consists of the following columns:

  • Status - shows whether the statement was executed successfully or not
  • Query Text - displays the text of the query
  • Database - gives the name of the database where the statement was executed
  • Size (in bytes) - displays the size of the query
  • Executed On - shows the timestamp of execution
  • Duration - shows the duration of execution
  • File - provides the statement file name
  • Server - gives the name of the server where the statement was executed
  • User - shows who initiated the execution

The SQL Query History filters

Each column has a separate set of filters that can help you navigate among many entries. To open the filter window, point to the column name and click the funnel icon:

SQL Query History column filters

1. Status

Choose the status of the queries to be displayed in the SQL Query History grid.

SQL Query History column filters

2. Query Text

Filter the entries either by choosing from the options in the Values tab or using the Text Filters. When using the latter, select the filter from the list and enter the value you are looking for.

SQL Query History column filters

3. Size (in bytes)

Select the minimum and maximum values to filter the entries. To do that, you can either use the slider on the Values tab or switch to the Numeric Filters to fine-tune the filter.

SQL Query History column filters

4. Executed On

Select the date range from the list on the Values tab or configure the filter using the Date Filters tab.

SQL Query History column filters

5. Duration

Select the query duration from the list on the Values tab or configure the filter using the Date Filters tab.

SQL Query History column filters

6. File

Select the desired file from the list on the Values tab. Alternatively, go to the Text Filters tab and configure the filter according to your needs.

SQL Query History column filters

7. Server

Choose one or more servers from the list on the Values tab or go to the Text Filters tab and configure it according to your needs.

SQL Query History column filters

8. User

Choose a user from the list on the Values tab. Alternatively, configure the Text Filters according to your needs.

SQL Query History column filters

Above the grid, you will find:

  • a search bar, where you can search out required queries by text
  • From: and To: fields to specify the desired log output timeframe
  • the number of query executions found in your search

Additionally, there is the Export to CSV button that allows you to export the SQL Query History in a CSV file.

The SQL Query History shortcut menu offers the following options:

Execution shortcut menu

  • Sort Ascending - apply ascending sort order to any column
  • Sort Descending - apply descending sort order to any column
  • Clear All Sorting - reset sorting to default
  • Group By This Column - group completed statements by the selected column
  • Show/Hide Group By Box - drag a column header to the Group By Box to group data by that column
  • Hide This Column - hide the selected column
  • Column Chooser - drag and drop columns to remove them from the grid or add them back at your convenience
  • Best Fit - adjust the width of a column to fit the value with maximum characters
  • Best Fit (all columns) - ditto for all columns
  • Filter Editor - editor with rich filtering options
  • Show/Hide Auto Filter Row - add a row to filter data directly from the grid

Show auto filter row

Note

SQL Query History is stored in a file called ExecutedQueries.db. In order to specify a path to the folder where this file will be stored, proceed to the SQL Complete menu -> Options -> SQL Query History. The default path is %appdata%\Devart\dbForge SQL Complete\ExecutedQueries.

Please note that this file does not store your connection passwords.

Filter Editor

1. To create a custom filter, right-click the SQL Query History grid and select Filter Editor.

2. In the window that opens, choose the operators that fit your needs:

SQL Query History context menu

Here, you can also add a condition, a group of conditions, or clear all the previously set ones. An alternative way to add a condition is to click a plus button next to the operator.

SQL Query History context menu

3. By clicking the conditions in blue and green, configure the filter according to your needs.

4. Click Apply.

SQL Query History Customization

In case you would like to remove one or more columns from the SQL Query History grid, you can use the Customization window.

1. Right-click the SQL Query History grid and select Column Chooser.

SQL Query History context menu

2. To customize the layout, drag and drop the columns from SQL Query History to the window that opens.

SQL Query History customization window

3. In order to return the column, drag it back to the SQL Query History grid.

SQL Query History enables the following actions:

1. Success verification check

This option allows the user to check whether a SQL statement has been successfully executed. Each query in the table has a field with an icon that displays the result of the execution. It is also possible to sort the table entries by this parameter.

2. Log output range

SQL Query History provides the ability to set the date interval in which the queries were output. It is possible to view the entire list for any period, with any selected time range.

3. Group by column

This option allows grouping the completed statements by any required column in the table. To do this, just drag the selected column to the panel above the list. The SQL Query History will display the results immediately.

4. Open in a new SQL window or copy to clipboard

Each user can get access to any SQL statement from the archive to view its code to perform any changes after. To do it, simply right-click a required request and select To New SQL Window or To Clipboard.

5. Sort statements

The feature facilitates various sorting of executed statements. The archive can be sorted by any column that is present in the table. To do it, simply click the header of the required column.

6. Detailed item info

The SQL Query History has a convenient interface for getting all the necessary information about each statement. It is easy to sort information by each parameter field as well as perform customization of the displayed columns. Just select the Column Chooser menu item after right-clicking blank space in the grid header and drag-and-drop the columns.

7. Search filter

This feature allows filtering executed statements by the text key entry. This option will be very useful for DBAs who often have to work with a large number of requests. Simply enter the required characters in the Search field, and SQL Query History will display the results immediately.

8. Export to CSV

The SQL Query History can also be exported to the CSV file format.

SQL Query History options

To set up the SQL Query History options, proceed to Options from the SQL Complete menu. These include:

  • Checkbox to enable/disable SQL Query History tracking
  • Modifiable options: Store executed queries within… (60 days by default), Max script size to store (1024 KB by default), and Show history at startup from… (the last week by default)
  • Field with the path to the folder where the history is stored
  • Clear SQL Query History button.

Note

In the Express Edition of SQL Complete, SQL Query History is stored for the last 3 days only.

Execution warnings

There is always a risk of losing data and objects (whether accidentally or not) because of untested code, unwanted commands, various code leftovers, and errors that can damage data irreparably.

This is where execution warnings come into play. SQL Complete analyzes the script to be executed for the DELETE, DROP, TRUNCATE, and UPDATE statements, and produces a blocking message if the statements are used in certain types of environments. For instance, the message will be prompted if the user tries to execute a DELETE statement against a production database.

To configure the behavior of execution warnings:

  • Go to Options on the SQL Complete menu.
  • In the left pane of the Options window, click Execution Warnings.

Execution Warnings

In the Execution Warnings window, it is possible to specify the maximum script size to analyze and enable execution warnings for the required environments.

Note

SQL Complete analyzes every USE statement and prompts an execution warning if the statement refers to a database of the environment for which the execution warning is set.

To disable execution warnings for a certain piece of the code, use the following tags:

--nowarn
--endnowarn

Execution Warnings

Execution notifications

Execution notifications allow you to get informed about the query execution result.

Execution notifications

The notification contains the following information:

  • The name of the document

  • The duration of a query

  • Query execution status:

    - Query completed with errors

    - Query executed successfully

    - Query canceled

You can enable notifications and change settings on the Notifications tab of the Options menu. You can also set a query execution time limit to get notified about queries that exceed it. By default, notifications are on and the notification duration time is 7 seconds.

Execution notifications options

Note

If a query is running longer than 23:59:59 hours, days are added to the time in the Notification.

Transaction reminders

With this feature enabled, whenever there are open transactions during the query execution, a reminder will pop-up informing you about the number of uncommitted transactions.

The Transaction Reminder window contains the following information: a document name and a number of open transactions.

Transaction reminder

By default, notifications are on. To disable notifications, clear the Notify if execution contains open transactions check box on the Notifications tab of the Options menu.

Transaction reminder options

Note

The VIEW DATABASE STATE permission is required in the database for the feature to work properly.