Viewing Table Data
!!! info "Some of the features and screenshots in this section are for the DbVisualizer Pro edition." A table's data can be viewed in in various ways in the Data tab in its Object View tab.
Check Editing Table Data for information about the table data editor in DbVisualizer.
Check Viewing BSON Document Data for information about the document data viewer in DbVisualizer.
Opening the Data tab
To open the Data tab for a table:
- Locate the table in the Databases tab tree,
- Double-click the table node to open its Object View tab,
- Open the Data sub tab.
Each column width is automatically resized to match the column width, including the column header, by default. You can disable this behavior in the the Tool Properties dialog, in the Grid category under the General tab.
If Auto Resize Column Widths is enabled, the Max Column Width setting can be used to limit the column width so that an extremely wide column does not take up all space.
In the same Tool Properties category, you can also disable Show Grid Row Header, i.e. the row number shown to the left of the data rows, for read-only grids such as the Data tab in the DbVisualizer Free edition and result sets from joined tables.
The column headers correspond to the column names by default, but you can specify in the Tool Properties dialog, in the Grid category under the General tab, that you like to use the column alias instead. This is mostly useful for grids representing SQL Commander result sets, but may also be useful in the Data tab grid for some databases.
The Data tab contains a number of features for locating and focusing on just the data of interest as described in the following sections.
Sorting
You can sort the data grid based on the values in one or more columns:
- Click on a column header to sort the grid in ascending order on the values in that column, indicated by an up-arrow in the column header.
- Click the same column header again to sort in descending order, indicated by a down-arrow in the column header.
- Click a third time to show the data in the order it was received from the database. This removes the sort indicator.
To sort on more than one column, Ctrl-click (keep the Ctrl key pressed when clicking) on additional columns. The grid is then sorted on the values in the first column you clicked on (indicated with a 1 next to the arrow), and then all rows with the same value in the first column are sorted on the values in the second sort column (indicated with a 2 next to the arrow), and so on.
Formatting
Note that when a cell is selected, the format pattern used to display the cell value is visible at bottom of the grid. By clicking the blue link, the Data Formats section of Tool Properties will be opened.
Where Filter
You can use the filter capability in the Data tab to limit the number of rows shown in the grid, using the same syntax as for an SQL WHERE clause. The Filter menu button in the grid toolbar contains all operations related to using a filter.
The top entries in the menu are previously used filters for the table, if any. The checkbox is selected for the filter that is currently in use. The filters are saved between DbVisualizer sessions, and you can toggle between them by selecting them from the menu. The maximum number of filters to save is specified in the Tool Properties dialog, in the Table Data category under the General tab.
You use the Use No Filter choice to disable all filters for the table, and the Clear Filter List to permanently remove all filters for the table.
To create a new filter:
- Select Open Filter Editor to launch the Filter & Sort Configuration dialog,
- Select a column name, an operation and the value for the condition using the controls in the Filter area,
- Add the condition to the filter by clicking the AND or OR button, and create additional conditions in the same way if needed,
- Click the Use Filter button to apply the filter, save it and close the dialog, or close the dialog without applying and saving the filter by clicking the Close button.
You can use Ctrl-Enter while editing the value field to reload the grid with that single condition applied, or in the editor to reload the grid based on all filter conditions created so far.
The Sort area is similar to the Filter area. You can select column names and sort order from the two lists, and click the Add button to add the sort criteria for the single column to the complete criteria.
If you often need to tweak the filter conditions and want a more compact user interface, you can use the inline filter view. Use the Show/Hide Filter Area choice in the Filter menu to toggle the visibility of the inline filter.
Column Filter
Only in DbVisualizer Pro
This feature is only available in the DbVisualizer Pro edition.
The Column Filter acts on the data that is already loaded in the grid, as opposed to a Where Filter which is used to limit the number of rows fetched from the database. With a Column Filter, you can easily list only those rows having a column value matching the Column Filter. By customizing the filter it is possible to add more complex filters such as only listing rows where the column value does not contain a certain value or substring.
A Column Filter is added by clicking the right part of the column header. A column having a Column Filter is indicated by the presence of a filter icon in the column header.
It is possible to to add Column Filters to multiple columns. For cases where you don't care which column has a specific value we recommend the Quick Filter
Quick Filter
Only in DbVisualizer Pro
This feature is only available in the DbVisualizer Pro edition.
The Quick Filter acts on the data that is already loaded in the grid, as opposed to a Where Filter which is used to limit the number of rows fetched from the database. With a Quick Filter, you can easily list only those rows in the grid that match the entered search string.
Use the Quick Filter pull-down menu (click on the magnifying glass) to choose if the filter should match cells in all columns or just one selected column, case or case insensitive matching, and where in the cell the value must match.
For the Use wild cards option the following characters have special meaning:
?
- The question mark indicates there is zero or one of the preceding element. For example, colou?r matches both "color" and "colour". *
- The asterisk indicates there are zero or more of the preceding element. For example, ab*c matches "ac", "abc", "abbc", "abbbc", and so on. +
- The plus sign indicates that there is one or more of the preceding element. For example, ab+c matches "abc", "abbc", "abbbc", and so on, but not "ac".
Max Rows/Max Chars
DbVisualizer limits the number of rows shown in the Data tab to 1000 rows, by default. This is done to conserve memory. If this limit prevents you from seeing the data of interest, you should first consider:
- Using a Where Filter to only retrieve the rows of interest instead of all rows in the table,
- Exporting the table to a file
If you really need to look at more than 1000 rows, you can change the value in the Max Rows field in the grid status bar. Use a value of 0 or -1 to get all rows, or a specific number (e.g. 5000) to set a new limit.
Character data columns may contain very large values that use up lots of memory. If you are only interested in seeing a few characters, you can set the Max Chars field in the grid status bar to the number of characters you want to see.
You can define how to deal with columns that have more characters than the specified maximum in the Tool Properties dialog, in the Grid category under the General tab. You have two choices: Truncate Values or Truncate Values Visually.
- Truncate Values truncates the original value for the grid cell to be less then the setting of Max Chars.
This affects any subsequent edits and SQL operations that use the value since it's truncated. This setting is only useful to save memory when viewing very large text columns.
- Truncate Values Visually truncates the visible value only and leave the original value intact. This is the preferred setting since it will not harm the original value. The disadvantage is that more memory is needed when dealing with large text columns.
When the grid data is limited due to either the Max Rows or Max Chars value, you get an indication about this in the rows/columns field in the grid status bar and in the corresponding limit field. The color is also changed for the affected controls.
Along with the highlighted field, a warning pops up close to the field. You can disable this behavior in the Tool Properties dialog, in the General / Grid category.
Max Rows at First Display
By default, opening the Data tab for a table loads all rows, unless there is a Max Rows limit. If you have very large tables and don't want to risk memory issues if you accidentally open the Data tab and have no Max Rows limit, you can specify a Max Rows at First Display limit. You do this in the Tool Properties dialog, in the Table Data category under the General tab.
The default is -1, which means no limit. If you set it to a positive number, only the specified number of rows are loaded when the Data tab is first opened for a table. To load more rows, click the Reload button in the Data tab toolbar.
Column Header Tooltips
The column header tooltip shows data type information about the column. To see the tooltip, let the mouse hover over the column header. The tooltip pops up in about a second.
Highlight Primary Key Columns
By default, a Primary Key column is shown with an icon in the column header. You can disable this in the Tool Properties dialog, in the Table Data category under the General tab.
Auto Resize Columns
The column header right-click menu contains a number of options for automatic resizing of column widths.
Show Only Some Columns
The Grid Column Chooser dialog controls which columns you want to appear in a grid. Open the dialog by the right-click menu on the column header or the button above the vertical scrollbar in the grid. See the screenshot below.
Grid Column Chooser with the default layout. The icon in the top right corner of the grid indicates the standard layout. |
---|
Grid Column Chooser with a custom layout. The icon in the top right corner of the grid indicates that the layout is changed. |
The Grid Column Chooser dialog shows all columns that are available in the grid. The checkmark in front of a column name indicates that the column is visible in the grid, while an unchecked box indicates that it is excluded from the grid. Click the checkmark to change the visibility of a column. You can change the visibility for all columns at once using the two visibility buttons in the dialog.
The order of the columns can also be adjusted in this dialog. Just select one or several rows and use the Up and Down buttons to move the rows up (left in the grid) or down (right in the grid). You can also use Drag&Drop to move the rows.
If you want to revert your changes, you can click on the Default Layout button to reset the grid, i.e., making all columns visible and putting them in their default locations.
Modifications of column visibility, size, and order are saved between invocations of DbVisualizer for the Log tab and all grids in the various Object View tabs except for the Data tab.
If you modify the column visibility in the Data tab, the changes persist throughout the session. For instance, if you remove the ACTOR_ID column in the Data tab for the table Actor, the ACTOR_ID column remains excluded when you reload the table or come back to the Data tab for that table later in the same session. You must manually make it visible again to bring it back. The changes are, however, reset when you restart the application. If you modify the column visibility in the Log tab in the SQL Commander, the changes will affect the Log tab immediately and other SQL Commander Log tabs the next time they are opened or refreshed by running a query. Any custom column setups are saved individually for the Log tab in SQL Commander, Actions, Export (all but export grid), Import, and Procedure Editor.
Right-Click Menu Operations
The right-click menu for the grid contains a lot of operations for working with the data without changing it. In addition to the common select, copy, and print operations, some operations that may require a bit of an explanation are described below. Examples are based on the following simple grid that contains 9 cells on 3 rows with 3 columns, where the first two rows are selected:
Operation | Description |
---|---|
Copy Selection | Copy all selected cells onto the system clipboard. Example: 1 A c3 |
Copy Selection with Column Header | Copy all selected cells including column header onto the system clipboard. Example: col1 col2 col3 |
Copy Selection as Formatted Text | Copy all selected cells including column header in fixed width columns onto the system clipboard. Example: col1 col2 col3 |
Copy Selection as Comma List | Copy all selected cells onto the system clipboard, formatted as Comma Separated Values (CSV), one row for each column. Example: 1, 2 |
Copy Selection as IN Clause | Copy all selected cells onto the system clipboard, formatted as an IN clause. Example: (col1 IN (1, 2)) |
Copy Selection as IN Clause with AND (ALL) | Copy all selected cells onto the system clipboard, formatted as a clause to select rows where ALL selected values match . Example: (col1 = 1 and col2 = 'A' and col3 = 'c3')This is different from Copy Selection as IN Clause with OR(ANY) since the clause matches all column values on each row; it will not select a row with column values 1, B, c3 . |
Copy Selection as IN Clause with OR (ANY) | Copy all selected cells onto the system clipboard, formatted as a clause to select rows where ANY of the selected values match. Example: (col1 IN (1, 2))This is similar to Copy Selection as IN Clause, but is slightly more efficient in some situations; in this example, col3 is matched using equals (= ) rather than IN since the selected rows have the same value in this column. This is different from Copy Selection as IN Clause with AND (ALL) since the clause matches any column value on each row; it will select a row with column values 1, B, c3 . |
Copy Selection as HTML Table | Copy all selected cells onto the system clipboard, formatted as HTML code. Paste this into an HTML-capable editor to get a nicely formatted table. |
Copy Selection as JIRA Table | Copy all selected cells onto the system clipboard, formatted as markup code suitable for JIRA and similar tools. Paste this into a suitable editor to get a nicely formatted table. |
Save Selected Cell | Save the value of the selected cell to a file, selected with a file chooser dialog |
Compare | Compare the data in this grid to the data in other open grids. |
Compare Selected Cells | Compare the data in the two cells as text |
Reset Grid | Reset the visual state of the grid (does not affect the column settings or the actual data in the grid). |
Browse Row in Window | Display all data for the selected row in a separate window. Note: for a read/write grid, this entry is named Edit Row in Window. |
Browse Cell in Window | Display the cell value in a separate window. This is especially useful for BLOB/CLOB data. Note: for a read/write grid, this entry is named Edit Cell in Window. |
Show in Navigator | Open the Navigator tab with the current selections and sorting. |
Describe Data | Show detailed information about the columns in the grid. |
Aggregation Data for Selection | Displays aggregation data for the current selection. Read more in Aggregation Data for Selection below. |
Generate Filter & Sort | The operations in this submenu help you create common Where Filters. |
Extract Filter & Sort as SELECT | Creates a SELECT statement based on the visible columns, filters and sorting in the grid to the clipboard. |
Create Row Count Data Monitor | Creates a monitor for tracking the row count in the table over time. |
Create Row Count DIff Data Monitor | Creates a monitor for tracking the number of added or removed rows in the table over time. |
Script to SQL Commander
There are also a set of operations for generating SQL statements based on the current selection. Choosing any of these creates the appropriate SQL and then switches the view to a new SQL Commander tab. You must use these operations to edit table data in the DbVisualizer Free edition. With the DbVisualizer Pro edition, you can instead use inline and form based editing.
Operation | SQL Example |
---|---|
Script: SELECT ALL | SELECT * FROM COUNTRY; |
Script: SELECT ALL WHERE | select * FROM SAKILA.COUNTRY |
Script: SELECT ALL WITH FILTER | select * |
Script: INSERT INTO TABLE | insert into SAKILA.COUNTRY |
Script: INSERT COPY INTO TABLE | insert into SAKILA.COUNTRY |
Script: UPDATE WHERE | update SAKILA.COUNTRY |
Script: DELETE WHERE | delete from SAKILA.COUNTRY |
You can generate SQL with either static values as they appear in the grid, or with DbVisualizer variables. A variable is essentially a placeholder for a value in an SQL statement. When the statement is executed, DbVisualizer locates all variables and presents them in a dialog where you can enter or modify values for the variables. DbVisualizer replaces the variable placeholders with the new values before executing the statement.
Variables are used in the generated SQL statements by default. You can disable the Include Variables in SQL setting in the Tool Properties dialog, in the Table Data category under the General tab, to use literal values are instead.
Here is an example of the SQL generated for Script: SELECT ALL WHERE with the Include Variables in SQL setting enabled, assuming the table is named SAKILA.COUNTRY and has a column named COUNTRY with the value 'Brazil' on the selected row:
SELECT * FROM COUNTRY WHERE COUNTRY = ${COUNTRY (where)||Brazil||String||where ds=50 dt=VARCHAR}$;
And here is the same example with the Include Variables in SQL setting disabled:
SELECT * FROM COUNTRY WHERE COUNTRY = 'Brazil';
Creating Monitors
A monitor in DbVisualizer is an SQL query executed at a specified frequency so you can track changes in data over time. The result can be viewed either as a grid or a graph. The right-click menu for the grid in the Data tab contains operations for creating two common types of monitors for the table: a Row Count Data monitor or a Row Count Diff Data monitor. The first tracks the number of rows in the table over time and the second tracks the number of added or removed rows over time. Please read more about monitors in Monitoring Data Changes.
Aggregation Data for Selection
Only in DbVisualizer Pro
This feature is only available in the DbVisualizer Pro edition.
The Aggregation Data for Selection feature presents aggregation data organized per data type on the current selection in a grid. It provides information about cells holding numbers, text, date/time information and more. The following is an example of what it shows:
With Auto Update checked, the data is updated automatically when you change the selection in the grid. For very large selections, you may prefer to disable this feature and instead click Update when you want to refresh the data. Click a link (blue underlined text) in the aggregation table to locate and highlight the actual value in the source data grid. The Handle Number Values in Text Types as Numbers setting simply treats all valid numbers in text data types as numbers and include them in the Number Count summary.