Exporting a Schema
Only in DbVisualizer Pro
This feature is only available in the DbVisualizer Pro edition.
You can export all or selected objects in a schema using the Export Schema assistant.
To export a schema:
- Select the schema node in the Databases tab tree,
- Launch the Export Schema assistant from the right-click menu,
- Select an Output Format, Output Destination, Objects to export and Options,
- Click Export.
Output Format
You can export objects in one of these formats: CSV, HTML, SQL,TXT, XML, XLS (Excel), or JSON.
The CSV, HTML, TXT, XLS and JSON formats are specifically for table data and are not supported for any other type of objects.
The SQL and XML formats can be used for all objects to export the DDL, and for tables you can also choose to include the table data in these formats.
You can control whether to use delimited identifiers and/or qualified names in the DDL and INSERT statements generated for the SQL format, using the controls in the Options area.
Output Destination
The destination can be one of:
- a file,
- an open or new SQL Commander tab, with options for where in an open SQL Commander to insert the result,
- to the system clipboard.
Object Types
In the Object Types area you select what to export. You can check the checkbox for an object type to export all objects of that type, or expand a type node and select individual objects. To select all objects, just check the checkbox for the schema itself at the top of the tree.
Options
The Options section contains options common to all Output Formats at the top, followed by options for the selected format.
Example of the options for SQL:
Example of the options for Excel:
For the SQL and XML formats, you can choose to export the DDL, the DDL for indexes for a table and the table data: as INSERT statements for the SQL statement or in one of three XML formats.
For the Excel format, you can choose to export table data as either in the XLSX (default) or the legacy XLS format.
Most formats also let you specify other options, such as delimiters, title and descriptions. Just select an Output Format to see which options are available. All options are described in the context of the @export command, as the Export dialog is just a GUI for the command.
You can adjust the Data Formats specifically for the exported table data. By default, the formats defined in Tool Properties are used, but sometimes you need to export dates and numbers in a different format because you intend to import the data into a different type of database.
If you are exporting table data in the SQL format from one database type (e.g. Oracle) to import it in a database of a different type (e.g. PostgreSQL) by executing the generated script, you need to be aware of differences in the literal formats for Date, Time and Timestamp data. If you connect to the other database using a JDBC client like DbVisualizer, you can select the JDBC escape format for these data format. This generates literals that the JDBC driver converts into a format the target database can interpret.
In the Data Format Settings dialog you can also specify how to quote text data and how to handle quotes within the text value.
Using Variables in Fields
You can use some of the pre-defined DbVisualizer variables (${dbvis-date}$, ${dbvis-time}$, ${dbvis-timestamp}$, ${dbvis-connection}$, ${dbvis-database-type}$ and ${dbvis-object}$) in all fields that hold free text (e.g. title and description fields) and as part of the file name field.
Use the ${dbvis-object}$ variable as part of the filename if you want to export the DDL and/or data to a separate file for each object. The variable is replaced with the object type and object name, e.g. ${dbvis_object}$.sql becomes table_COUNTRIES.sql for a table named COUNTRIES.
Saving And Loading Settings
If you often use the same settings, you can save them as the default settings for this assistant. If you use a number of common settings, you can save them to individual files that you can load as needed. Use the Settings drop-down button menu to accomplish this:
- Save as Default Settings Saves all format settings as default. These are then loaded automatically when open an Export Schema dialog
- Use Default Settings Use this choice to initialize the settings with default values
- Remove Default Settings Removes the saved defaults and restores the regular defaults
- Load... Use this choice to open the file chooser dialog, in which you can select a settings file
- Save As... Use this choice to save the settings to a file
- Copy Settings to Clipboard Copies the settings to the system clipboard
- Copy Settings to Clipboard Use this choice to copy all settings to the system clipboard. These can then be pasted into the SQL Commander to define the settings for the @export editor commands.