14. Database Object Explorer

14.1. Objects tab
14.2. Table details
14.3. Table data
14.4. View details
14.5. Procedure tab
14.6. Search tables

The Database Object Explorer displays the available database objects such as Tables, Views, Triggers and Stored Procedures.

There are three ways to start the DbExplorer
Using ToolsDatabase Explorer.
Passing the paramter -dbexplorer to the main program (sqlworkbench.sh or SQLWorkbench.exe)
When using Windows with the DbExplorer.exe executable or in Linux/Unix using shell script dbexplorer.sh

At the top of the window, the current schema (for MS SQL Server this would be the current database, for Oracle this is the user) can be selected.

The displayed list can be filtered using the quick filter above the list. To filter the list by the object name, simply enter the criteria in the filter field, and press ENTER or click the filter icon . The criteria field will list the last 25 values that were entered in the dropdown. If you want to filter based on a different column of the list, right-click on the criteria field, and select the desired column from the Filtercolumn menu item of the popup menu. The same filter can be applied on the Procedures tab.

The list of tables can be pre-filtered to remove unwanted entries such as tables that have been deleted and now reside in Oracle's "Recycle Bin". The filtering is done through a regular expression on a per-database basis. By default this is only defined for Oracle and will filter out any table that starts with BIN$.

For Oracle (and other DBMS that support them), synonyms are displayed as well. You can filter out unwanted synonyms by specifying a regular expression in your workbench.settings file. This filter will also be applied when displaying the list of available tables when opening the command completion popup.

The first tab displays the structure of tables and views. The type of object displayed can be chosen from the drop down right above the table list. This list will be returned by the JDBC driver, so the available "Table types" can vary from DBMS to DBMS.

The menu item Database Explorer will either display the explorer as a new window, or a new panel, depending on the system options.

You can always open additional DbExplorer tabs or windows using
ToolsNew DbExplorer panel.
ToolsNew DbExplorer window.

14.1. Objects tab

The object list displays tables, views, sequences and synonyms (basically anyhting apart from procedures or functions). The context menu of the list offers several additional functions:

Export data

This will execute a WbExport command for the currently selected table(s). Choosing this option is equivalent to do a SELECT * FROM table; and then executing SQLExport query result from the SQL editor in the main window. See the description of the WbExport command for details.

Put SELECT into

This will put a SELECT statement into the SQL editor to display all data for the selected table. You can choose into which editor tab the statement will be written. The currently selected editor tab is displayed in bold (when displaying the DbExplorer in a separate window. You can also put the generated SQL statement into a new editor tab, by selecting the item New tab

Create empty INSERT

This creates an empty INSERT statement for the currently selected table(s). This is intended for programmers that want to use the statement inside their code.

Create default SELECT

This creates a SELECT for the selected table(s) that includes all columns for the table. If you want to generate a SELECT statement to actually retreive data from within the editor, please use the Put SELECT into option.

Create DDL Script

With this command a script for multiple objects can be created. Select all the tables, views or other objects in the table list, that you want to create a script for. Then right click and select "Create DDL Script". This will generate one script for all selected items in the list.

When this command is selected, a new window will be shown. The window contains a statusbar which indicates the object that is currently processed. The complete script will be shown as soon as all objects have been processed. The objects will be processed in this order:

SEQUENCEs
TABLEs
VIEWs
SYNONYMS

Create schema report

This will create an XML report of the selected tables. You will be prompted to specify the location of the generated XML file. This report can also be generated using the WbReport command.

Drop

Drops the selected objects. If at least one object is a table, and the currently used DBMS supports cascaded dropping of constraints, you can enable cascaded delete of constraints. If this option is enabled SQL Workbench/J would generate e.g. for Oracle a DROP TABLE mytable CASCADE CONSTRAINTS. This is necessary if you want to drop several tables at the same time that have foreign key constraints defined.

Delete data

Deletes all rows from the selected table(s) by executing a DELETE FROM table_name; to the server for each selected table. If the DBMS supports TRUNCATE then this can be done with TRUNCATE as well. Using TRUNCATE is usually faster as no transation state is maintained.

The list of tables is sorted according to the sort order in the table list. If the tables have foreign key constraints, you can re-order them to be processed in the correct order by clicking on the Check foreign keys button.

14.2. Table details

When a table is selected, the right part of the window will display its column definition, the SQL statement to create the table, any index defined on that table (only if the JDBC driver returns that information), other tables that are referenced by the currently selected table, any table that references the currently selected table and any trigger that is defined on that table.

The column list will also display any comments defined for the column (if the JDBC driver returns the information). Oracle's JDBC driver does not return those comments by default. To enable the display of column comments (remarks) you have to supply an extended property in your connection profile. The property's name should be remarksReporting and the value should be set to true.

If the DBMS supports synonyms, the columns tab will display the column definition of the underlying table or view. The source tab will display the statement to re-create the synonym. If the underlying object of the synonym is a table, then indexes, foreign keys and triggers for that table will be displayed as well. Note that if the synonym is for a view, those tabs will still be displayed, but will not show any information.

14.3. Table data

The data tab will display the data from the currently selected table. There are several options to configure the display of this tab. The Autoload check box, controls the retrieval of the data. If this is checked, then the data will be retrieved from the database as soon as the table is selected in the table list (and the tab is visible).

The data tab will also display a total row count of the table. As this display can take a while, the automatic retrieval of the row count can be disabled. To disable the automatic calculation of the table's rowcount, click on the Settings button and deselect the checkbox Autoload table row count. To calculate the table's row count when this is not done automatically, click on the Rows label. You can cancel the row count retrieval while it's running by clicking on the label again.

The data tab is only available if the currently selected objects is recognized as an object that can can be "SELECTED". Which object types are included can be defined in the settings for SQL Workbench/J See selectable object types for details.

You can define a maximum number of rows which should be retrieved. If you enter 0 (zero) then all rows are retrieved. Limiting the number of rows is useful if you have tables with a lot of rows, where the entire table would not fit into memory.

In addition to the max rows setting, a second limit can be defined. If the total number of rows in the table exceeds this second limit, a warning is displayed, whether the data should be loaded.

This is useful when the max rows parameter is set to zero and you accidently display a table with a large number of rows.

If the automatic retrieval is activated, then the retrieve of the data can be prevented by holding down the Shift key while switching to the data tab.

The data in the tab can be edited just like the data in the main window. To add or delete rows, you can either use the buttons on the toolbar in the upper part of the data display, or the popup menu. To edit a value in a field, simply double click that field, start typing while the field has focus (yellow border) or hit F2 while the field has focus.

14.4. View details

When a database VIEW is selected in the object list the right will display the columns for the view, the source and the data returned by a select from that view.

The data details tab works the same way as the data tab for a table. If the view is updateable (depends on the view definition and the underlying DBMS) then the data can also be changed within the data tab

The source code is retrieved by customized SQL queries (this is not supported by the JDBC driver). If the source code of views is not displayed for your DBMS, please contact .

14.5. Procedure tab

The procedure tab will list all stored procedures and functions stored in the current schema. For procedures or functions returning a result set, the definition of the columns will be displayed as well.

To display the procedure's source code SQL Workbench/J uses its own SQL queries. For most popular DBMS systems the necessary queries are built into the application. If the procedure sourc is not displayed for your DBMS, please contact the author.

Functions inside Oracle packages will be listed separately on the left side, but the source code will contain all functions/procedures from that package.

14.6. Search tables

This tab offers the ability to search for a value in all text columns of all tables which are selected. The results will be displayed on the right side of that tab. The result will always display the complete row where the search value was found. Any column that contains the entered value will be highlighted.

The value will be used to create a LIKE 'value' restriction for each text column on the selected tables. Therefore the value should contain a wildcard, otherwise the exact expression will be searched.

You can apply a function to each column as well. This is useful if you want to to do a case insensitive search on Oracle (Oracles VARCHAR comparison is case sensitive). In the entry field for the column the placeholder $col$ is replaced with the actual column name during the search. To do a case insensitive search in Oracle, you would enter lower($col$) in the column field and '%test%' in the value field.

The expression in the column field is sent to the DBMS without changes, except the replacement of $col$ with the current column name. The above example would yield a lower(<column_name>) like '%test%' for each text column for the selected tables.

The generated SQL statements are logged in the second tab, labelled SQL Statements.

In the resulting tables, SQL Workbench/J tries to highlight those columns which match the criteria. This might not always work, if you apply a function to the column itself such as to_upper() SQL Workbench/J does not know that this will result in a case-insesitive search on the database. SQL Workbench/J tries to guess if the given function/value combination might result in a case insensitive search (especially on a DBMS which does a case sensitive search by default) but this might not work in all the cases and for all DBMS.