Progress
Database Administration
Guide and Reference
SQLSCHEMA Utility
A command-line utility that writes SQL-92 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 the Progress SQL-92 Guide and Reference for information about SQL Explorer.
Before you can execute SQLSCHEMA against a database server, the server must be configured to accept SQL connections and must be running. See "Creating and Deleting Databases," for instructions on creating a database and "Starting Up and Shutting Down," for information about starting a Progress database or database server.
SYNTAX
-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.
-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-92. 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.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.database_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 database_name. This parameter is required and must be the last parameter specified. The database name is specified in a connection string, such as db-type:
T
:host:portnum:dbname.
The SQLSCHEMA utility cannot write definitions for Progress 4GL 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.
EXAMPLESThis 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:
This example directs the SQLSCHEMA utility to write table definitions to an output file named
salesdbschema.dfsql
:
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-out in Unicode UTF-8.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |