Extracting Metadata Using isql
Go Up to Command-line isql Tool
You can extract the DDL statements that define the metadata for a database to an output file with the -extract option. Adding the optional -output flag reroutes output to a named file. Use this syntax:
isql [[-extract | -x][-a] [[-output | -o] outputfile]] database;
The -x option is an abbreviation for -extract. The -a flag directs isql to extract all database objects. Note that the output file specification, <outputfile>, must follow the -output flag, while you can place the name of the database being extracted at the end of the command.
| Option | Description |
|---|---|
|
<database> |
File specification of the database from which metadata is being extracted |
|
<outputfile> |
File specification of the text file to receive the extracted statements; if omitted, |
You can use the resulting text file to:
- Examine the current state of a database’s system tables before you plan alterations to it, or when a database has changed significantly since its creation.
- Use your text editor to make changes to the database definition or create a new database source file.
The -extract option does not extract UDF code and Blob filters, because they are not part of the database. It does extract the declarations to the database (with DECLARE EXTERNAL FUNCTION and DECLARE FILTER).
The -extract option also does not extract system tables, system views, or system triggers.
Because DDL statements do not contain references to object ownership, the extracted file does not show ownership. The output file includes the name of the object and the owner if one is defined. There is no way to assign an object to its original owner.
For a list of the order of extraction of metadata objects, see Extracting Metadata.For example, the following statement extracts the system catalogs from the database employee.ib to a file called employee.sql:
isql -extract -output employee.sql employee.ib;
The resulting output script is created with -commit following each set of commands, so that tables can be referenced in subsequent definitions. This command extracts all keywords and object names in uppercase when possible (some international metadata has no uppercase).
To extract DDL statements from database employee.ib and store in the file
employee.sql, enter:
isql -a employee.ib -output employee.sql
The following example extracts the DDL statements from the database dev.ib:
isql -x dev.ib
This example combines the -extract and -output options to extract the DDL statements from the database dev.ib into a file called dev.out. The output database name must follow the -output flag.
isql -extract -output dev.out dev.ib