query OpenEdge 10.1B database metadata - Forum - Community Groups - Progress Community

query OpenEdge 10.1B database metadata

 Forum

query OpenEdge 10.1B database metadata

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

  • 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

    information.

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

    sections.

    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.

  • 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