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.

SELECT * FROM sysprogress.systables WHERE tbl like 'sys%';

Find columns in a table

SELECT * FROM sysprogress.syscolumns_full WHERE tbl = 'department';

Show all the indexes on a table

SELECT * FROM sysprogress.sysindexes WHERE tbl like 'department';

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

SELECT tt.*, t.tbl
FROM sysprogress.systblstat tt
INNER JOIN sysprogress.systables t ON tt.tblid = t.id AND tt.property = 4
ORDER BY value DESC;

Find primary index in a table – two methods.

SELECT "_index-name" FROM "_index" WHERE ROWID = (SELECT prime_index FROM sysprogress.systables_full WHERE tbl = 'customer');
SELECT "_index-name" FROM "_index" WHERE ROWID = (SELECT "_prime-INDEX" FROM "_file" WHERE "_file-name" = 'customer');

Find primary index in all tables

SELECT t.tbl, i."_index-name"
FROM "_index" i
INNER JOIN sysprogress.systables_full t ON i.ROWID = t.prime_index
WHERE t.owner = 'pub' AND t.id > 0;

Show indexes in all tables

SELECT * FROM sysprogress.sysindexes WHERE tblowner = 'pub' ORDER BY tbl, idxname, idxseq;

Show me tables that start with an underscore

SELECT * FROM sysprogress.systables WHERE tbl LIKE '\_%' ESCAPE '\' ORDER BY tbl;

Find the name of all sequences and how they get incremented

SELECT * FROM sysprogress.syssequences;

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

SELECT * FROM sysprogress.SYS_CHKCOL_USAGE;
SELECT * FROM sysprogress.SYS_CHK_CONSTRS;

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

SELECT SYSDATE, SYSTIME, SYSTIMESTAMP, SYSTIMESTAMP_tz FROM sysprogress.SYSCALCTABLE;

Show all constrains on tables and columns.

SELECT * FROM sysprogress.SYS_KEYCOL_USAGE;
SELECT * FROM sysprogress.SYS_TBL_CONSTRS;

Find all the foreign key constraints

SELECT * FROM sysprogress.SYS_REF_CONSTRS;

List all the connections to the database

SELECT * FROM "_connect";
SELECT * FROM "_myconnection"; -- my own connection information

DB specific information

Show the features enabled in the database

SELECT * FROM "_database-feature";

Find the GUID of the DB

SELECT * FROM "_db-detail";

What are the parameters enabled on the DB

SELECT * FROM "_dbparams";

Find the block size being used on the DB

SELECT * FROM "_dbstatus";

Ports that are being used by the DB

SELECT * FROM "_servers";

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