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 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 system options.
will either display the explorer as a new window, or a new panel, depending on theYou can always open additional DbExplorer tabs or windows using
→ . |
→ . |
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:
This will execute a
WbExport
command 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.
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
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.
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 thePut SELECT into
option.
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
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.
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.
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 supportsTRUNCATE
then this can be done withTRUNCATE
as well. UsingTRUNCATE
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
button.
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.
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 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.
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>
.
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.
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.