Try OpenEdge Now
skip to main content
SQL Development
OpenEdge SQL Data Definition Language : Working with SQL utilities : Using the SQLSCHEMA utility
 

Using the SQLSCHEMA utility

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 shows how to connect using the SQLSchema utility with a blank password.
sqlschema -o myOutputFile -t userid.myview -u userid -a \'\' progress:T:localhost:port:sports2000
Note: \'\' should be used when connecting using a blank password
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 OpenEdge Data Management: Database Administration.
* Schema definition for multi-tenant tables