Sample Queries for the Extended System Catalogs for SQL
You can issue queries to get information from the extended system catalogs. Each query specifies the class code for the type of object being selected.
Example: Find Information on Every Report in the Database
This query finds information on every report in the database.
select report=o.object_name, o.object_owner,
o.short_remark, r.reptype
from ii_objects o, ii_reports r
where (o.object_class = 1501 or
o.object_class = 1502 or
o.object_class = 1511)
/* object_classes 1501, 1502, 1511 = reports
*/
and o.object_id = r.object_id
Example: Find the Name and Tabbing Sequence Number of Fields on a Form
This query finds the name and tabbing sequence number of every simple field and table field on form “empform” (empform is owned by user “susan”).
select form=o.object_name, f.fldname, f.flseq,
f.fltype
from ii_objects o, ii_fields f
where o.object_class = 1601
/* object_class 1601 = "form" */
and o.object_name = 'empform'
and o.object_owner = 'susan'
and o.object_id = f.object_id
and (f.fltype = 0 or f.fltype = 1)
/* simple field or table field */
order by flseq
Example: Find Information on Every ABF Application
This query finds information on every ABF application in the database.
select appname=object_name, object_owner
from ii_objects o
where o.object_class = 2001
/* object_class 2001 = "abf application" */
Example: Find Information on All Frames and Procedures in an Application
The following two queries require two correlation variables on the table ii_objects. Two variables are required, because we need to find all the frames and procedures in the application, plus object information on the selected frames and procedures.
This query finds information on all frames and procedures in application lab.
select appname=o.object_name, o2.object_class,
2.object_name, o2.object_owner, o2.short_remark
from ii_objects o, ii_abfobjects a,
ii_objects o2
where o.object_name = 'lab'
and o.object_class = 2001
/* object_class 2001 = "abf application" */
and o.object_id = a.applid
and a.object_id = o2.object_id
This query finds dependency information for all frames and procedures in application payables. Frames and procedures with no dependencies show up as a row with ad.name=DUMMY.
select appname=o.object_name, o2.object_class,
o2.object_name, o2.object_owner,
o2.short_remark, ad.abfdef_name,
ad.abfdef_deptype, ad.object_class
from ii_objects o, ii_objects o2,
ii_abfobjects a, ii_abfdependencies ad
where o.object_name = 'payables'
and o.object_class = 2001
/* object_class 2001 = "abf application" */
and o.object_id = a.applid
and a.object_id = o2.object_id
and a.object_id = ad.object_id
order by object_name
Example: Select Object Information
This query selects object information and long remarks, when available, by performing an outer join of ii_objects with ii_longremarks.
select o.object_name, o.object_class,
o.object_owner, o.short_remark, l.long_remark
from ii_objects o, ii_longremarks l
where o.object_id = l.object_i
union all
select o.object_name, o.object_class,
o.object_owner, o.short_remark, ''
from ii_objects o
where not exists
( select *
from ii_longremarks
where ii_longremarks.object_id = o.object_id )
order by object_name
Last modified date: 01/30/2023