Inspecting OpenEdge metadata with SQL

Inspecting OpenEdge metadata with SQL

OpenEdge’s virtual system tables (VSTs) provide excellent statistics that help in database development and maintenance. This article shows a few SQL statements that help reveal information about the database.

inspecting openedge metadata with sql

SQL to query metadata

Show all VST tables that contain metadata.

Find columns in a table

Show all the indexes on a table

Assuming UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN has been run, get # of records for each table.

Find primary index in a table – two methods.

Find primary index in all tables

Show indexes in all tables

Show me tables that start with an underscore

Find the name of all sequences and how they get incremented

Where do we have check constraints and what are those contraints?

Is there a table like dual table Oracle has? Yes. OpenEdge has sysprogress.syscalctable.

Show all constrains on tables and columns.

Find all the foreign key constraints

List all the connections to the database

DB specific information

Show the features enabled in the database

Find the GUID of the DB

What are the parameters enabled on the DB

Find the block size being used on the DB

Ports that are being used by the DB

As you have noticed, VSTs provide incredibly valuable information that allows systems to be monitored and managed well.

Be sure to see other topics listed on Modern web applications and API with OpenEdge, SQL, ODBC and PHP