26. Show information about database objects

26. Show information about database objects
Prev		Next

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 https://www.sql-workbench.eu/xstl.html. If you have XSLT stylsheets that you would like to share, please send them to <support@sql-workbench.net>.


	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:

Parameter	Description
-file	The 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 `TABLE`s and `VIEW`s are included. To include e.g. `SYSTEM VIEW`s and `TEMPORARY TABLE`s, 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.
-includeTables	Controls 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.
-includeTableGrants	If tables are included in the output, the grants for each table can also be included with this parameter. The default value is `false`.
-includeProcedures	Control 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 source 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`.
-styleSheet	Apply a XSLT transformation to the generated XML file.
-xsltOutput	The 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 - WbDescribe

WbDescribe shows the definition of the given table.

Alternatively it can be written as DESC or DESCRIBE.

The command expects the name of a table or view 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).

WbDescribe 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 WbDescribe 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:

Parameter	Description
-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:

Parameter	Description
-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.

26.9. Show dependency information - WbListDependencies

Lists dependency information about database objects. It is the command line version of the "Used by" and "Uses" information in the DbExplorer and DbTree.

The command supports the following parameters:

Parameter	Description
-name	Then name of the database object for which the dependencies should be displayed. This parameter supports wildcards, e.g. `-name=p%` to display dependencies for all objects that start with a `P`. This parameter supports auto-completion.
-schema	Search objects in the specified schema. This parameter supports auto-completion.
-catalog	Search objects in the specified catalog (database). This parameter supports auto-completion.
-objectType	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. This parameter supports auto-completion.
-dependency	Define the type of dependency to display. Valid values are `uses` and `using`. This parameter supports auto-completion.

Prev		Next
25. SQL Workbench/J to generate DDL commands	Home	27. Manage macros with SQL Workbench/J command


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