Database Administrator Guide > Database Administrator Guide > C. System Catalogs > Extended System Catalogs > Sample Queries for the Extended System Catalogs for SQL
Was this helpful?
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.
For details on class codes, see Object Classes in the ii_objects Catalog.
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: 08/29/2024