query OpenEdge 10.1B database metadata

Posted by Mark.Ward on 22-Nov-2010 07:43

How would I go about getting the database metadata for tables, column names, data types, and column width using SQL?

All Replies

Posted by Mark.Ward on 22-Nov-2010 08:11

I have found the documentation that states the tables that contain the metadata but I do not find them in the database.

OpenEdge SQL maintains a set of system tables for storing information about tables, columns,

indexes, constraints, and privileges.

All users have read access to the system catalog tables. SQL Data Definition Language (DDL)

statements and GRANT and REVOKE statements modify system catalog tables. The system tables

are modified in response to these statements, as the database evolves and changes.

The owner of the system tables is sysprogress. If you connect to a OpenEdge SQL environment

with a username other than sysprogress, you must use the owner qualifier when you reference

a system table in a SQL query. Alternatively, you can issue a SET SCHEMA sysprogress statement

to set the default username for unqualified table names to sysprogress.

Core tables store information on the tables, columns, and indexes that make up the database.

The remaining tables contain detailed information on database objects and statistical


Table 4 lists the system catalog tables in the same order that they are presented in following


Table 4: System tables and descriptions (1 of 3)

System table Summary description

SYSTABLES Core system table. One row for each TABLE in the database.

SYSCOLUMNS Core system table. One row for each COLUMN of each table

in the database.

SYSINDEXES Core system table. One row for each component of each

INDEX in the database.

SYSCALCTABLE A single row with a single column set to the value 100.

SYSNCHARSTAT One row for each CHARACTER column in the database.

Posted by Mark.Ward on 22-Nov-2010 15:08

I found the solution.

It was not clear to me from the documentation but as the documentation stated put sysprogress. in front of each table name.

select * from sysprogress.SYSCOLUMNS

This thread is closed