The SQLSCHEMA utility is a command‑line utility that writes SQL database schema components to an output file selectively. You can capture table definitions including table constraints, views, stored procedures including related privileges, and triggers. At the command line you specify which components to dump.

The extended support for multi-tenancy includes domain name, name of the tenant, and sequence name utility option in the SQLSCHEMA utility to write SQL database schema definitions for domains, tenants, and sequences respectively. The existing table utility option is enhanced to support writing definitions for a multi-tenant table.

The SQLSCHEMA utility also supports writing definitions for multi-tenant groups.

Use the following syntax for the SQLSCHEMA utility:

Syntax 

sqlschema -u user_name
 [ -a password]
 [ -t [owner_name]table_name1   
   [owner_name]table_name2, ...]
 [-t [owner_name]view_name1   
   [owner_name]view_name2, ...]
 [ -p [owner_name]procedure_name, ...]
 [ -T [owner_name]trigger_name, ...]
 [ -G [owner_name]procedure_name, ...]
 [ -g [owner_name]table_name, ...]
 [ -s [owner_name]table_name, ...]
 [ -o output_file_name]
 [ -d domain_name]
 [ -n tenant_name]
 [ -q sequence_name]
 [ -r group_name]
 [ -z ]database_name
The following example demonstrates how to establish a connection using the SQLSCHEMA utility:
sqlschema -o myschema t %.% -u <dba_user> -a <dba_password> progress:T:<hostname>:<database port>:<database name>
Note: When connecting with a blank password, you can specify the password as either '' (two single quotes) or \'\'. Both represent an empty string and are interpreted as a valid blank password by the SQL utility.

The SQLSCHEMA utility accepts all encoding prefixes supported by the genpassword utility for encoding passwords before transmitting them over the network to the OpenEdge SQL server for authentication. If the password is provided in plain text, it is encoded using ae2h4 before being transmitted over the network.

For more information, see genpassword and Encoding prefix.

For example, if the password for connecting to the database using the SQLSCHEMA utility is progress, you can generate the encoded credentials with the ae2h1 prefix as follows: 
genpassword -password progress -prefix ae2h1
As a result, the genpassword utility generates an ae2h1 encoded password:
ae2h1::e41027ea0710535e4bd4e2987d5647c95c1dd9988a7b5a4c18ec208017e7b67a9f4546afa452ff0d332f1fa703101923
You can now connect to the database using the SQLSCHEMA utility by running the following command:
sqlschema -o myschema -t %.% -u user1 
-a ae2h1::e41027ea0710535e4bd4e2987d5647c95c1dd9988a7b5a4c18ec208017e7b67a9f4546afa452ff0d332f1fa703101923 
progress:T:localhost:9889:test

Table definitions include the database area name for the table, derived from a scan of the area and objects. When SQLSCHEMA writes a table definition, it does not automatically write associated triggers, synonyms, or privileges. These must be explicitly specified on the command line. Capturing database schema requires privileges to access the requested components.

Example: SQLSCHEMA utility for writing object definitions

The following example directs the SQLSCHEMA utility to write table definitions and trigger information. The output goes to the screen since no output_file_name is specified. Since the user_name and password are not specified, SQLSCHEMA will prompt the user for these values.

sqlschema -t tucker.customers,tucker.products -T
tucker.customers,tucker.products progress:T:thunder:4077:salesdb

Example: SQLSCHEMA for writing object definitions to output file

The following example directs the SQLSCHEMA utility to write table definitions to an output file named salesdbschema.dfsql.

sqlschema -u tucker -a sulky -t %.cust%,%.invent%,%.sales% -o
salesdbschema.dfsql progress:T:thunder:4077:salesdb
Note: Each output file created by the SQLSCHEMA utility records character set information about the contents of the file. When you use SQLSCHEMA to dump schema information from a database, the schema is written in Unicode UTF‑8.

For more information on SQL utilities and database administration, see Manage OpenEdge Databases.