22. Database Object Explorer

22.1. Objects tab
22.2. Table details
22.3. Modifying the definition of database objects
22.4. Table data
22.5. Changing the display order of table columns
22.6. Customize data retrieval
22.7. Customizing the generation of the table source
22.8. View details
22.9. Procedure tab
22.10. Search table data

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 and/or catalog can be selected. Whether both dropdowns are available depends on the current DBMS. For Microsoft SQL Server, both the schema and the database can be changed. The labels next to the dropdown are retrieved from the JDBC driver and should reflect the terms used for the current DBMS (Schema for PostgreSQL and Oracle, Owner and Database for SQL Server, Database for MySQL).

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.

Synonyms are displayed if the current DBMS supports them. 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. If a DbExplorer is already open (either a window or a tab), the existing one is made visible (or active), when using this menu item.

You can open any number of additional DbExplorer tabs or windows using ToolsNew DbExplorer panel or ToolsNew DbExplorer window

22.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.

When using this function, the customization for datatypes is not applied to the generated SELECT statement.

Count rows

This will count the rows for each selected table object. The rowcounts will be opened in a new window. This is the same functionality as the WbRowCount command.

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

When using this function, the customization for datatypes will be applied to the generated SELECT statement.

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 empty UPDATE

This creates an empty UPDATE 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. This feature is intended for programmers who want to put a SELECT statement into their code.

If you want to generate a SELECT statement to actually retrieve data from within the editor, please use the Put SELECT into option.

When using this function, the customization for datatypes is not applied to the generated SELECT statement.

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 the order: SEQUENCES, TABLES, VIEWS, SYNONYMS.

The same script can also be generated using the WbGenerateScript command.

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 WbSchemaReport 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.

If the current DBMS does not support a cascading drop, you can order the tables so that foreign keys are detected and the tables are dropped in the right order by clicking on the Check foreign keys button.

If the checkbox "Add missing tables" is selected, any table that should be dropped before any of the selected tables (because of foreign key constraints) will be added to the list of tables to be dropped.

Generate DROP script

This creates a script that first removes all incoming foreign keys to the selected tables, the necessary DROP statements and the statements to re-create the foreign keys.

For more details, please refer to the description of the WbGenerateDrop statement.

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.

If the checkbox "Add missing tables" is selected, any table that should be deleted before any of the selected tables (because of foreign key constraints) will be added to the list of tables.

ALTER script

After you have changed the name of a table in the list of objects, you can generate and run a SQL script that will apply that change to the database.

For details please refer to the section Changing table definitions

22.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 not for a view, those tabs will still be displayed, but will not show any information.

22.3. Modifying the definition of database objects

[Important]

Applying changes to the definition of a table (or other database objects) is only possible if the necessary ALTER statements have been configured. For most of the major DBMS these statements are already built into SQL Workbench/J.

If your changes are rejected (e.g. while changing a table name or the datatype of a column), please make sure that you have enabled the option Allow table altering. If that option is enabled and your DBMS does support the change you were trying to do, please send a mail with the necessary information to the support email address.

22.3.1. Changing the table definition

You can edit the definition of the columns, add new columns or delete existing columns directly in the list of columns. To apply the changes, click on the Apply DDL button.

22.3.2. Renaming objects

You can change the name of a table (or other objects if the DBMS supports that) directly in the object list. For DBMS that support it, you can also edit the remarks column of the table to change the documentation.

Once you have changed a name (or several) the menu item "ALTER Script" in the context menu of the object list will be enabled. Additionally a button Apply DDL will appear in the status bar of the object list. Both will bring up a window with the necessary SQL statements to apply your changes. You can save the generated script to a file or run the statements directly from that window.

22.4. 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.

22.5. Changing the display order of table columns

You can re-arrange the display order of the columns in the data tab using drag & drop. If you want to apply that column order whenever you display the table data, you can save the column order by right-clicking in the table header and then using the menu item Save column order. If the column order has not been changed, the menu item is disabled.

The column order will be stored using the fully qualified table name and the current connection's JDBC URL as the lookup key.

To reset the column order use the menu item Reset column order from the popup menu. This will revert the column order to the order in which the columns appear in the source table. The saved order will be deleted as well.

22.6. Customize data retrieval

When displaying the data for a table, SQL Workbench/J generates a SELECT statement that will retrieve all rows and columns from the database. In some cases the data for certain data types cannot be displayed correctly as the JDBC drivers might not implement a proper "toString()" method that converts the data into a readable format.

You can customize the SELECT statement that is generated by SQL Workbench/J when retrieving table data in the DbExplorer in the configuration file workbench.settings. For each DBMS you can define an expression for specific data types that are used when building the SELECT statement.

To configure this, you need to add one line per data type and DBMS to the file workbench.settings:

workbench.db.[dbid].selectexpression.[type]=expression(${column})

When building the SELECT statement, the placeholder ${column} will be replaced with the actual column name. [dbid] is the DBID of the DBMS for which the replacement should be done.

The whole key (the part to the left of the equal sign) must be in lowercase.

[type] is the datatype of the column without any brackets or parameters: varchar instead of varchar(10), or number instead of number(10,2)

To convert e.g. the geometry datatype of postgres to a readable format, one would use the following expression astext(transform(geo_column,4326)).

To tell the DbExplorer to replace the retrieval of columns of type geometry in PostgreSQL with the above expression, the following line in workbench.settings is necessary:

workbench.db.postgres.selectexpression.geometry=astext(transform(${column},4326)) 

For e.g. the table geo_table (id integer, geo_col geometry) SQL Workbench/J will generate the following SELECT statement:

SELECT id, astext(transform(geo_col,4326))
FROM geo_table

to retrieve the data of that table.

Note that the data of columns that have been "converted" through this mechanism, might not be updateable any more. If you intend to edit such a column you will have to provide a column alias in order for SQL Workbench/J to generate a correct UPDATE or INSERT statement.

Another example is to replace the retrieval of XML columns. To configure the DbExplorer to convert Oracle's XMLTYPE a string, the following line in workbench.settings is necessary:

workbench.db.oracle.selectexpression.xmltype=extract(${column}, '/').getClobVal()

To convert DB2's XML type to a string, the following configuration can be used:

workbench.db.db2.selectexpression.xml=xmlserialize(${column} AS CLOB)

The column name (as displayed in the result set) will usually be generated by the DBMS and will most probably not contain the real column name. In order to see the real column name you can supply a column alias in the configuration.

workbench.db.oracle.selectexpression.xmltype=extract(${column}, '/').getClobVal() AS ${column}

In order for SQL Workbench/J to parse the SQL statement correctly, the AS keyword must be used.

You can check the generated SELECT statement by using the Put SELECT into feature. The statement that is generated and put into the editor, is the same as the one used for the data retrieval.

The defined expression will also be used for the Search table data feature, when using the server side search. If you want to search inside the data that is returned by the defined expression you have to make sure that you DBMS supports the result of that expression as part of a LIKE expression. E.g. for the above Oracle example, SQL Workbench/J will generate the following WHERE condition:

WHERE to_clob(my_clob_col) LIKE '%searchvalue%'

22.7. Customizing the generation of the table source

SQL Workbench/J re-generates the source of a table based on the information about the table's metadata returned by the driver. In some cases the driver might not return the correct information, or not all the information that is necessary to build the correct syntax for the DBMS. In those cases, a SQL query can be configured that can use the built-in functionality of the DBMS to return a table's definition.

This DBMS specific retrieval of the table source is defined by three properties in workbench.settings. Please refer to Customize table source retrieval for details.

22.8. 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 .

22.9. 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.

22.10. Search table data

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.

[Note]

The results displayed here are not editable. If you want to modify the results after a search, you have to use the WbGrepData command

Two different implementations of the search are available: server side and client side.

22.10.1. Server side search

To server side search is enabled by selecting the checkbox labelled "Server side search".

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, labeled 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.

The SELECT statement that is built to display the table's data will list all columns from the table. If the table contains BLOB columns this might lead to a substantial memory consumption. To avoid loading too many data into memory, you can check the option "Do not retrieve LOB columns". In that case columns of type CLOB or BLOB will not be retrieved.

SQL Workbench/J is building a SELECT that "searches" for data using a LIKE expression. Only columns of type CHAR and VARCHAR are included in the LIKE search, because that is what most DBMS support. If the DBMS you are using supports LIKE expressions for other datatypes as well, you can configure this datatypes to be included in the search feature of the DbExplorer.

22.10.2. Client side search

To client side search is enabled by un-checking the checkbox labelled "Server side search".

The client side search retrieves every row from the server, compares the retrieved values for each row and keeps the rows where at least one column matches the defined search criteria.

As opposed to the server side search, this means that every row from the selected table(s) will be sent from the database server to the application. For large tables were only a small number of the rows will match the search value this can increase the processing time substantially.

As the searching is done on the client side, this means that it can also "search" data types that cannot be using for a LIKE query such as CLOB, DATE, INTEGER.

The search criteria is defined similar to the definition of a filter for a result set. For every column, its value will be converted to a character representation. The resulting string value will then be compared according to the defined comparator and the entered search value. If at least one column's value matches, the row will be displayed. The comparison is always done in a case-insesitively. The contents of BLOB columns will never be searched.

The character representation that is used is based on the default formatting options from the Options Window. This means that e.g. a DATE column will be compared according to the standard formatting options before the comparison is done.

The client side search is also available through the WbGrepData command