26. Show information about database objects

26.1. Create a report of the database objects - WbSchemaReport
26.2. Show table structure - DESCRIBE
26.3. List tables - WbList
26.4. List indexes - WbListIndexes
26.5. List stored procedures - WbListProcs
26.6. List triggers - WbListTriggers
26.7. List catalogs - WbListCat
26.8. List schemas - WbListSchemas
[Important]

All SQL Workbench/J specific command can only be used from within SQL Workbench/J

26.1. Create a report of the database objects - WbSchemaReport

Creates an XML report of selected tables. This report could be used to generate an HTML documentation of the database (e.g. using the XSLT command). This report can also be generated from within the Database Object Explorer

The resulting XML file can be transformed into a HTML documentation of your database schema. Sample stylesheets can be downloaded from http://www.sql-workbench.net/xstl.html. If you have XSLT stylsheets that you would like to share, please send them to .

[Note]

To see table and column comments with an Oracle database, you need to enable remarks reporting for the JDBC driver, otherwise the driver will not return comments. To see the "comment" values from SQL Server's extended properties, please setup the property retrieval as described here

The command supports the following parameters:

ParameterDescription
-fileThe filename of the output file.
-objects

A (comma separated) list of objects to report. Default is all objects that are "tables" or views. The list of possible objects corresponds to the objects shown in the "Objects" tab of the DbExplorer.

If you want to generate the report on tables from different schemas you have to use fully qualified names in the list (e.g. -tables=shop.orders,accounting.invoices) You can also specify wildcards in the table name: -table=CONTRACT_% will create an XML report for all tables that start with CONTRACT_.

This parameter supports auto-completion.

-schemas

A (comma separated) list of schemas to generate the report from. For each user/schema all tables are included in the report. e.g. -schemas=public,accounting would generate a report for all tables in the schemas public and accounting.

If you combine -schemas with -objects, the list of objects will be applied to every schema unless the object names are supplied with a schema: -schemas=accounting,invoices -objects=o*,customers.c* will select all objects starting with O from the schemas accounting,invoices and all objects starting with C from the schema customers.

The possible values for this parameter correspond to the "Schema" dropdown in the DbExplorer. The parameter supports auto-completion and will show a list of available schemas.

-types

A (comma separated) list of "table like" object types to include. By default TABLEs and VIEWs are included. To include e.g. SYSTEM VIEWs and TEMPORARY TABLEs, use the following option: -types='TABLE,VIEW,SYSTEM VIEW,TEMPORARY TABLE'. If you include type names that contain a space (or e.g. a dash) you have to quote the whole list, not just the single value.

The default for this parameter is TABLE,VIEW

The values for this parameter correspond to the values shown in the "types" dropdown in the "Objects" tab of the DbExplorer. The parameter supports auto-completion and will show a list of the available object types for the current DBMS.

You can include any type shown in the DbExplorer's Objects tab. To e.g. include domain and enum definitions for PostgreSQL use: -types=table,view,sequence,domain,enum

This parameter supports auto-completion.

-excludeObjectNames A (comma separated) list of tables to exclude from reporting. This is only used if -tables is also specified. To create a report on all tables, but exclude those that start with 'DEV', use -tables=* -excludeTableNames=DEV*
-objectTypeNames

This parameter can be repeated several times to define the object names per object type to be retrieved. The format of the argument is -objectTypeNames=typename:namepattern

The following will select the tables person and address, all sequences starting with "T" and the view v_person. If the list of object names contains a comma, the whole parameter value needs to be quoted:

-objectTypeNames='table:person,address' -objectTypeNames=sequence:t* -objectTypeNames=view:v_person

The type names are the same ones that can be used with the -types parameter. This can be combined with schema qualified names:

-objectTypeNames='table:cust.person,accounting.address' -objectTypeNames=view:public.*

This can also be used to restrict the retrieval of stored procedures: -objectNameTypes=procedure:P* will include all stored procedures (or functions) that start with a "P". In this case the parameter -includeProcedures is ignored.

If this parameter is used at least once, all of the following parameters are ignored: -types and -objects, -includeSequences, -includeTables and -includeViews are ignored.

The exclusion pattern defined through -excludeObjectNames is applied to all object types.

-includeTablesControls the output of table information for the report. The default is true. Valid values are true, false.
-includeSequences

Control the output of sequence information for the report. The default is false. Valid values are true, false.

Adding sequence to the list of types specified with the -types parameter has the same effect.

-includeTableGrantsIf tables are included in the output, the grants for each table can also be included with this parameter. The default value is false.
-includeProceduresControl the output of stored procedure information for the report. The default is false. Valid values are true, false.
-includeTriggers This parameter controls if table triggers are added to the output. The default value is true.
-reportTitle Defines the title for the generated XML file. The specified title is written into the tag <report-title> and can be used when transforming the XML e.g. into a HTML file.
-writeFullSource

By default the sourcce code for views is written as retrieved from the DBMS into the XML file. This might not be a complete create view statement though. When -writeFullSource=true is specified SQL Workbench/J will generate a complete create view statement, similar to the code that is shown in the DbExplorer.

The default is false. Valid values are: true, false.

-styleSheetApply a XSLT transformation to the generated XML file.
-xsltOutputThe name of the generated output file when applying the XSLT transformation.
-xsltParameter A list of parameters (key/value pairs) that should be passed to the XSLT processor. When using e.g. the wbreport2liquibase.xslt stylesheet, the value of the author attribute can be set using -xsltParameter="authorName=42". This parameter can be provided multiple times for multiple parameters, e.g. when using wbreport2pg.xslt: -xsltParameter="makeLowerCase=42" -xsltParameter="useJdbcTypes=true"

26.2. Show table structure - DESCRIBE

Describe shows the definition of the given table. It can be abbreviated with DESC. The command expects the table name as a parameter. The output of the command will be several result tabs to show the table structure, indexes and triggers (if present). If the "described" object is a view, the message tab will additionally contain the view source (if available).

DESC person;

If you want to show the structure of a table from a different user, you need to prefix the table name with the desired user DESCRIBE otheruser.person;

26.3. List tables - WbList

This command lists all available tables (including views and synonyms). This output is equivalent to the left part of the Database Object Explorer's Table tab.

You can limit the displayed objects by either specifying a wildcard for the names to be retrieved: WbList P% will list all tables or views starting with the letter "P"

The command supports two parameters to specify the tables and objects to be listed. If you want to limit the result by specifying a wildcard for the name and the object type, you have to use the parameter switches:

ParameterDescription
-objects

Select the objects to be returned using a wildcard name, e.g. -objects=P%

-types

Limit the result to specific object types, e.g. WbList -objects=V% -types=VIEW will return all views starting with the letter "V".

26.4. List indexes - WbListIndexes

This command will list all indexes defined on tables available to the current user.

The command supports two parameters to specify the tables and objects to be listed. If you want to limit the result by specifying a wildcard for the name and the object type, you have to use the parameter switches:

ParameterDescription
-schema

Show only indexes for the specified schema, e.g. -schema=somethingelse

-catalog

Show only indexes for the specified catalog e.g. -catalog=other_db

-tableName

Show only indexes for the tables specified by the parameter. The parameter value can contain a wildcard, e.g. -tableName=VP% lists the indexes for all tables starting with VP

-indexName

Show only indexes with the specified name. The parameter value can contain a wildcard, e.g. -indexName=PK% lists only indexes that start with PK

26.5. List stored procedures - WbListProcs

This command will list all stored procedures available to the current user. The output of this command is equivalent to the Database Explorer's Procedure tab.

You can limit the list by supplying a wildcard search for the name, e.g.:

WbListProcs public.p%

26.6. List triggers - WbListTriggers

This command will list all stored triggers available to the current user. The output of this command is equivalent to the Database Explorer's Triggers tab (if enabled)

26.7. List catalogs - WbListCat

Lists the available catalogs (or databases). It is the same information that is shown in the DbExplorer's "Database" drop down.

The output of this command depends on the underlying JDBC driver and DBMS. For MS SQL Server this lists the available databases (which then could be changed with the command USE <dbname>)

For Oracle this command returns nothing as Oracle does not implement the concept of catalogs.

This command calls the JDBC driver's getCatalogs() method and will return its result. If on your database system this command does not display a list, it is most likely that your DBMS does not support catalogs (e.g. Oracle) or the driver does not implement this feature.

This command ignores the filter defined for catalogs in the connection profile and always returns all databases.

26.8. List schemas - WbListSchemas

Lists the available schemas from the current connection. The output of this command depends on the underlying JDBC driver and DBMS. It is the same information that is shown in the DbExplorer's "Schema" drop down.

This command ignores the filter defined for schemas in the connection profile and always returns all schemas.