Accessing structural information from databases

With SQL commands, it is possible to access detailed information about the contents of a database, including the complete list of tables and for each table, the names and types of its fields. The model odbcinspectdb.mos printed below shows how to retrieve and display the structural information for a given database.

model "Analyze DB structure"
 uses "mmodbc"

 declarations
  tables: list of string
  pkeylist: list of string
  pkeyind: list of integer
  fnames: dynamic array(Fields: range) of string
  ftypes: dynamic array(Fields) of integer
  ftypenames: dynamic array(Fields) of string
 end-declarations

 setparam("SQLverbose",true)
 SQLconnect("personnel.sqlite")

! Retrieve list of database tables
 SQLtables(tables)
 forall(t in tables) do

 ! Retrieve primary keys
  SQLprimarykeys(t, pkeylist)
  writeln(t, " primary key field names: ", pkeylist)
  SQLprimarykeys(t, pkeyind)
  writeln(t, " primary key field indices: ", pkeyind)

 ! Retrieve table structure
  writeln(t, " has ", SQLcolumns(t,fnames,ftypes), " fields")
  res:=SQLcolumns(t,fnames,ftypenames)
  forall(f in Fields | exists(fnames(f)))
    writeln(f, ": ", fnames(f), " ", ftypes(f), ": ", ftypenames(f))

 ! Delete aux. arrays for next loop iteration
  delcell(fnames); delcell(ftypes); delcell(ftypenames)
 end-do

 SQLdisconnect
end-model