26. Database Object Explorer
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 → . |
Passing the paramter -dbexplorer to the main program (sqlworkbench.sh, SQLWorkbench.exe or SQLWorkbench64.exe) |
At the top of the window, the current schema and/or catalog can be selected. Whether both drop downs 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 drop down 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 drop down.
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 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 → or →
26.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
WbExportcommand for the currently selected table(s). Choosing this option is equivalent to do aSELECT * FROM table;and then executing → from the SQL editor in the main window. See the description of the WbExport command for details.When using this function, the customization for data types is not applied to the generated
SELECTstatement.
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
WbRowCountcommand.
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
When using this function, the customization for data types will be applied to the generated
SELECTstatement.
Create empty INSERT
This creates an empty
INSERTstatement 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
UPDATEstatement 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
SELECTfor 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 data types is not applied to the generated
SELECTstatement.
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 status bar 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 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
DROPstatements 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 supportsTRUNCATEthen this can be done withTRUNCATEas well. UsingTRUNCATEis usually faster as no transaction 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 button.
If the check box "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
26.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.
26.3. Modifying the definition of database objects
![[Important]](./images/important.png) | |
Applying changes to the definition of a table (or other database objects) is only possible if the necessary
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. |
26.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 button.
26.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 "" in the context menu of the object list will be enabled. Additionally a button 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.
26.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 row count, click on the
button and deselect the check box 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.
26.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 . 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 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.
26.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%'
26.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.
26.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 <support@sql-workbench.net>.
26.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 source 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.
26.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]](./images/note.png) | |
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.
26.10.1. Server side search
To server side search is enabled by selecting the check box labeled
"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-insensitive 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.
26.10.2. Client side search
To client side search is enabled by un-checking the check box labeled
"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-insensitively. 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