25. SQL Workbench/J to generate DDL commands

25.1. Generate SQL script for database objects - WbGenerateScript
25.2. Show the source of a table - WbTableSource
25.3. Show the source of a view - WbViewSource
25.4. Show the source of a stored procedures - WbProcSource
25.5. Show the source of a trigger - WbTriggerSource
25.6. Generate DROP statement with dependencies - WbGenerateDrop
25.7. Generate SQL script for foreign key constraints - WbGenerateFKScript
25.8. Generate a table definition from an import file - WbGenerateImpTable
25.9. Generate DELETE statements with dependencies - WbGenerateDelete
[Important]

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

25.1. Generate SQL script for database objects - WbGenerateScript

WbGenerateScript re-creates the SQL for objects in the database. It is the command line version of the Generate Script option in the DbExplorer

The command supports the following parameters.

ParameterDescription
-objects

A comma separated list of table (views or other objects), e.g. -objects=customer,invoice,v_turnover,seq_cust_id. The parameter supports specifying tables using wildcards -objects=cust%,inv%.

-exclude

A comma separated list of object names to be excluded from the generated script. The parameter supports wildcards -exclude=foo*,bar*.

-schemas

A comma separated list of schemas. If this is not specified then the current (default) schema is used. If this parameter is provided together with the -objects parameter, then the objects for each schema are retrieved. e.g. -objects=person -schemas=prod,test will show generate the SQL for the person table in both schemas.

The parameter supports auto-completion and will show a list of the available schemas.

-types

A comma separated list of object types e.g. -types=VIEW,TABLE. This parameter is ignored if -objects is specified. The possible values for this parameter are the types listed in the drop down of the "Objects" tab in the DbExplorer.

The parameter supports auto-completion and will show a list of the available object types for the current DBMS.

-file

Defines the outputfile into which all statements are written. If this is not specified, the generated SQL statements are shown in the message area. file.

-encoding

The encoding to be used when writing the output file.

-includeForeignKeys

By default WbGenerateScript will add all foreign key constraints of the processed tables to the end of the generated script. If this parameter is set to false no foreign key constraints will be generated.

The foreign keys can be generated in a separate step using the WbGenerateFKScript command.

-includeTriggers

If this parameter is is present (or set to true), then all triggers (for the selected schemas) will be retrieved as well. The default is false.

-includeProcedures

If this parameter is present (or set to true), then all procedures and functions (for the selected schemas) will be retrieved as well. The default is false.

-includeDrop

If this parameter is present (or set to true) a DROP statement will be generated for each object in the list.

-includeTableGrants This parameter controls the generation of table grants. The default value is true.
-useSeparator

If this parameter is present (or set to true), comments will be added that identify the start and end of each object. The default is false.

25.2. Show the source of a table - WbTableSource

This command will show the source for a single table. The name of the table is given as an argument to the command:

WbTableSource person;

It is possible to provide wildcards for the table name:

WbTableSource p%;

This command is mainly intended to quickly look at a table's source, e.g. when running in console mode. To generate the DDL for multiple tables including depending objects or to save the generated SQL into a file, please use WbGenerateScript.

25.3. Show the source of a view - WbViewSource

This command will show the source for a single view. The name of the view is given as an argument to the command:

WbViewSource current_orders;

The output of this command can be written to a file when using parameters:

WbViewSource -view=current_orders -file=/home/arthur/script/current_orders.sql;

The supported parameters are:

  • -view - the name of the view
  • -file - the name of the output file
  • -encoding - the encoding to be used for the file

25.4. Show the source of a stored procedures - WbProcSource

This command will show the source for a single stored procedure (if the current DBMS is supported by SQL Workbench/J). The name of the procedure is given as an argument to the command:

WbProcSource the_answer;

The output of this command can be written to a file when using parameters:

WbProcSource -procedure=the_answer -file=/home/arthur/scripts/answer_proc.sql;

The supported parameters are:

  • -procedure - the name of the procedure (or package)
  • -file - the name of the output file
  • -encoding - the encoding to be used for the file

25.5. Show the source of a trigger - WbTriggerSource

This command will show the source for a single trigger The name of the trigger is given as an argument to the command:

WbTriggerSource order_trigger;

The output of this command can be written to a file when using parameters:

WbTriggerSource -trigger=order_trigger -file=/home/arthur/scripts/order_trigger;

The supported parameters are:

  • -trigger - the name of the trigger
  • -file - the name of the output file
  • -encoding - the encoding to be used for the file

25.6. Generate DROP statement with dependencies - WbGenerateDrop

The command WbGenerateDrop can be used to generate a SQL script for a table that will drop all foreign keys referencing that table, then a DROP statement for that table and the statements to re-created the foreign keys referencing that table.

This is useful if you need to re-create a table but don't want to manually delete all referencing foreign keys, especially if the DBMS does not support a cascading DROP.

This is also available in the DbExplorer's context menu as "Generate DROP script".

The command supports the following parameters.

ParameterDescription
-tables

A comma separated list of tables, e.g. -tables=customer,invoice. The parameter supports specifying tables using wildcards -tables=cust%,inv%.

-includeCreate

Valid values: true, false

By default WbGenerateDrop will also add the statements to re-create the foreign keys of the table(s). By specifying -includeCreate=false only the statements to drop the foreign key and to drop the table(s) will be created.

-onlyForeignkeys

Valid values: true, false

When using -onlyForeignkeys=true, then only ALTER TABLE statements will be generate that will drop the foreign keys of all selected tables. No DROP TABLE and no statements to re-create the foreign keys will be generated. Setting this parameter to true implies -includeCreate=false.

-sortByType

Valid values: true, false

Usually the generated SQL script will be ordered by the type of statements. So first all statements to drop constraints will be listed, then the drop table statements, then the statements to re-create all foreign keys. When specifying -sortByType=false, then the script will be ordered by table: first all statements for the first table, then all statements for the second table and so on.

-outputFile

Defines the file into which all statements are written. If multiple tables are selected using the -tables parameter, all statements will be written into this file.

-outputDir

Specifies an output directory into which one script per selected table will be written. The script files are named drop_XXX.sql, where XXX is the name of the respective table. If this parameter is used, -outputFile is ignored.

If neither -outputFile nor -outputDir is specified, the output is written to the message panel.

25.7. Generate SQL script for foreign key constraints - WbGenerateFKScript

WbGenerateFKScript re-creates the foreign key constraints for the specified tables.

The command supports the following parameters.

ParameterDescription
-tables

The tables for which the foreign key constraints should be generated. This parameter accepts wildcards, e.g. -tables=*. To specify tables from multiple schemas, use -tables=order_mgmt.*, customer_mgmt.*

-exclude

A comma separated list of tables names to be excluded from the generated script. The parameter supports wildcards -exclude=foo*,bar*.

-file

Defines the output file into which all statements are written. If this is not specified, the generated SQL statements are shown in the message area. file.

-encoding

The encoding to be used when writing the output file.

25.8. Generate a table definition from an import file - WbGenerateImpTable

WbGenerateImpTable analyzes an import file and generates a suitable CREATE TABLE statement to create a table with a structure that matches the import file, so that the file can be imported into that table.

By default this command will only check the first 1000 lines of the input file, assuming that the values are distributed evenly. If the data types for the columns do not reflect the real data, the sample size needs to be increased.

The generated table definition is intended for a quick way to import the data and thus the column definitions are likely to be not completely correct or optimal.

The command supports the following parameters.

ParameterDescription
-file

Specifies the input file to be analyzed. The filename may contain wildcards. When specifying e.g.: -file=/temp/export/*.txt one table for each text file found in the directory /temp/export will be created.

-lines

Defines the number of lines to analyze. The default is 1000 if this parameter is not specified

A value of 0 (zero) or less, results in parsing the entire file.

-type

Possible values: text, ods, xls, xlsx

The type of the import file. The valid types are the same as for WbImport. The XML type is not supported.

To import spreadsheet files, the necessary additional libraries must be installed.

-useVarchar

Possible values: true, false

If enabled, all columns will be created as varchar.

By default WbGenerateImpTable will try to create columns with a data type appropriate for the data found in the import file. If the input data is highly irregular or very skewed, creating all columns as varchar will make sure that all rows can be imported without any conversion error.

-delimiter

The delimiter for text files.

-quoteChar

The quote character for text files.

-encoding

The encoding for text files.

-header

Specifies if the input file contains column headers.

-dateFormat

The format for date columns.

-timestampFormat

The format for timestamp columns in the input file.

-decimal

The character used as the decimal separator.

-outputFile

By default the generated SQL statement is shown in the message area. If -outputFile is specified, the generated SQL statement will be written to that file.

-sheetNumber

If the input file is a spreadsheet, this parameter defines the sheet number to be analyzed. The first sheet has the number 1.

When specifying * for the sheet number a CREATE TABLE statement for every sheet will be created.

-table

The table name to use (or create)

-runScript

Possible values: true, false

By default, the CREATE TABLE statement is only generated and displayed. If -runScript=true is specified, the generated SQL script will be executed immediately.

By default, this will display a dialog to confirm the execution the CREATE TABLE statement. This confirmation can be suppressed using the parameter -prompt=false. In this case the generated statement will be run directly.

25.9. Generate DELETE statements with dependencies - WbGenerateDelete

The command WbGenerateDelete can be used to generate a SQL script for one or more rows that should be deleted including all rows from referencing tables (if foreign key constraints are defined)

This is also available through the menu item DataGenerate delete script which will generate the delete for the selected row(s) in the current result.

The command supports the following parameters.

ParameterDescription
-table

Specifies the root table of the hierarchy from which to delete the rows.

-columnValue

Defines the expression for each PK column to select the rows to be deleted. The value for this parameter is the column name followed by a colon, followed by the value for this column or an expression.

e.g.: -columnValue="person_id:42" will select rows where person_id has the value 42.

You can also specify expressions instead: -columnValue="id:<0" or -columnValue="id:in (1,2,3)".

For a multi-column primary key, specify the parameter multiple times: -columnValue="person_id:100" -columnValue="address_id:200".

-includeCommit

If true a COMMIT statement will be appended to the generated script.

-outputFile

The file into which the generated statements should be written. If this is omitted, the statements are displayed in the message area.

-appendFile

Valid values: true, false

If true, the statements are appended to an existing file. Otherwise any existing file will be overwritten.

-formatSql

Valid values: true, false

If true, the generated SQL will be formatted using the SQL formatter. If false each statement will be written as a single line.

To generate a script that deletes the person with ID=42 and all rows referencing that person, use the following statement:

WbGenerateDelete -table=person -columnValue="id:42";

To generate a script that deletes any person with an ID greater than 10 and all rows referencing those rows, use the following statement:

WbGenerateDelete -table=person -columnValue="id: > 10";

To generate a script that deletes rows from the film_category where the primary key consists of the columns film_id and category_id:

WbGenerateDelete -table=person -columnValue="film_id: in (1,2,5)" -columnValue="category_id: in (7,3,5);