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.
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