A command-line utility that writes SQL database schema components to an output file selectively.

Syntax

sqlschema -u user_name[ -a password]
          [ -t [owner_name.]table_name1 [,owner_name.]table_name2, ...]
          [ -p [owner_name.]procedure_name, ...]
          [ -T [owner_name.]trigger_name, ...]
          [ -G [owner_name.]procedure_name, ...]
          [ -g [owner_name.]table_name, ...]
          [ -s [owner_name.]tablename, ...]
          [ -o output_file_name]
          [ -d domain_name]
          [ -n tenant_name]
          [ -q sequence_name]
          db_name

Parameters

-u user_name

Specifies the user id that SQLSCHEMA employs to connect to the database. If you omit the user_name and password, SQLSCHEMA prompts you for these values. If you omit the user_name and supply a password, SQLSCHEMA uses the value defined by the USER environment variable.

-a password

Specifies the password used by the database for authentication.

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 aedh0 before being transmitted over the network.

For more information, see genpassword and Encoding prefix.

Note: If the server is running a database of OpenEdge release 12.8 or earlier, it does not recognize the aedh0 encoding prefix and cannot decode passwords encoded with it. The connection fails with an error message.

-t owner_name.table_name

A list of one or more tables you want to capture definitions for. Pattern matching is supported, using a percent sign (%) for multiple characters and an underscore (_) for a single character. The pattern matching follows the standard for the LIKE predicate in SQL. You can write the definition for a single table, a set of tables, or all tables. If you omit the optional owner_name table qualifier, SQLSCHEMA uses the name specified by the -u parameter.

-p owner_name.procedure_name

A list of one or more procedures you want to capture definitions for. The SQLSCHEMA utility supports pattern matching for multiple and single characters. See the owner_name.table_name parameter for an explanation of pattern matching. You can capture the definitions for a single procedure, a set of procedures, or all procedures. If you omit the optional owner_name table qualifier, SQLSCHEMA uses the name specified by the -u parameter.

-T owner_name.trigger_name

A list of one or more triggers you want to capture definitions for. The SQLSCHEMA utility supports pattern matching for multiple and single characters. See the owner_name.table_name parameter for an explanation of pattern matching. You can capture the definition for a single trigger, a set of triggers, or all triggers. If you omit the optional owner_name table qualifier, SQLSCHEMA uses the name specified by the -u parameter.

-G owner_name.procedure_name

Allows you to dump privileges on stored procedures in the form of GRANT statements.

-g owner_name.table_name

A list of one or more tables whose related privileges are captured as grant statements. You can write grant statements for both column and table privileges. The utility supports pattern matching for this parameter.

-s owner_name.table_name

Specifies a list of one or more tables whose related synonyms are captured as create synonym statements. The utility supports pattern matching for this parameter.

-o output_file_name.dfsql

Specifies the output file where SQLSCHEMA writes the definitions. When specified, the file extension name must be .dfsql. If output_file_name is omitted, SQLSCHEMA writes the definitions to the screen.

-d domain_name

Specifies the domain name of a multi-tenant database where SQLSCHEMA writes the definitions.

-n tenant_name

Specifies the tenant name of a multi-tenant database where SQLSCHEMA writes the definitions.

-q sequence_name

Specifies the sequence name of a multi-tenant database where SQLSCHEMA writes the definitions.

db_name

Identifies the database from which SQLSCHEMA captures component definitions. You can process a single database each time you invoke SQLSCHEMA. There is no option flag preceding the db_name. This parameter is required and must be the last parameter specified. The database name is specified in a connection string, such as progress:T:localhost:demosv:jo.

SQLSCHEMA 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. To load database schema information into a database, use the SQL Explorer tool. See OpenEdge Data Management: SQL Reference for information about SQL Explorer.

The SQLSCHEMA utility cannot write definitions for ABL tables. 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

This 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, as shown:

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

This 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 

This example directs the SQLSCHEMA utility to write schema definitions for two domains OpenEdgeA and OpenEdgeB respectively:

sqlschema -u mtuser@mtdomain -a mtuser -d OpenEdgeA, OpenEdgeB   
progress:T:localhost:2222:sportsdb

This example directs the SQLSCHEMA utility to write schema definitions for domains with Access as its last 6 characters:

sqlschema -u mtuser@mtdomain -a mtuser -d  %Access  
progress:T:localhost:2222:sportsdb

This example directs the SQLSCHEMA utility to write schema definitions for domains with OpenEdge as its first 8 characters followed by a single character:

sqlschema -u mtuser@mtdomain -a mtuser -d OpenEdge_ 
progress:T:localhost:2222:sportsdb

This example directs the SQLSCHEMA utility to write schema definitions for tenants T1 and T2 respectively:

sqlschema -u mtuser@mtdomain -a mtuser -n T1, T2 
progress:T:localhost:2222:sportsdb

This example directs the SQLSCHEMA utility to write schema definitions for the two sequences seq1 and seq2:

sqlschema -u mtuser@mtdomain -a mtuser -Q seq1, seq2 
progress:T:localhost:2222:sportsdb

This example directs the SQLSCHEMA utility to write schema definitions for sequences that start with seq followed by a single character:

sqlschema -u mtuser@mtdomain -a mtuser -Q seq_ 
progress:T:localhost:2222:sportsdb

Notes

• Before you can run SQLSCHEMA against a database server, the server must be configured to accept SQL connections and must be running.
• 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.